Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto Indexing (Access 2002)

    This is a followup question the the discussion in a previous post about Tools Options. I thought it might be better as a new post. It was recommended to turn off Auto Indexing and manually set indexes. How much Auto indexing does Access do and by disabling this will this suppress indexing of the primary key fields?

    Carla

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Indexing (Access 2002)

    AutoIndex means that if you create a new field in a table and give it a name that begins or ends with one of the items in the AutoIndex option, Access will automatically create an index on that field. The same holds for fields in tables imported from other applications such as Excel. The disadvantage of AutoIndex is that Access will create lots of indexes on fields that don't really need it. Each index takes up space, and keeping it up-to-date takes time, so large numbers of unneeded indexes will have a negative effect on performance.

    Access always keeps existing indexes on tables up-to-date, this has nothing to do with AutoIndex.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto Indexing (Access 2002)

    The short answer is Yes it does switch off indexing for Autonumber, which defeats the point, but you can still set it manually. I suppose the advice is there so that designers do not over index fields.

    general rule is not to index fileds that hold similar data ie Mr Mrs or Male Female etc Over indexing can slow your database down considerably

    primary key fields are the must for indexing
    Jerry

  4. #4
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Indexing (Access 2002)

    So this would probably explain something that I saw earlier this week. I work on several different databases in collaboration with two other programmers and I cannot always be sure who might have done what. I wanted to add a field to an existing table and kept getting an error message about indexes. I went into table design and there were 8 indexes set for the table. I removed the ones I thought to be unnecessary and was successful in adding the new field. I could not imagine that any of the others doing database work would have set all of these indexes. I have seen the auto indexing options and not really known what they do. As always thank you Hans for helping me learn more about Access. I try to read all of the posts daily and have found this forum my best learning resource bar none.

    Carla

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto Indexing (Access 2002)

    carla

    Just as a matter of professional interest. Are you collaboraters in the same company? If so do you use the same naming conventions, as I have set my AutoIndex on Import/Create: to just ID so that always generates for my primary key which are like EmployID, JobID or OrderID.
    Jerry

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Indexing (Access 2002)

    The primary key in a table is an index by definition. There is no need to use AutoIndex for that. AutoIndex does NOT create a primary key.

  7. #7
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Indexing (Access 2002)

    Hi Jerry,

    Well................good question. I work for a State department as a contractor. The databases I work with have been the creation of many developers. I oldest one I can find history on has been in daily use since 1998. Some of those past developers have been very good about using the naming convention and some not so good. At any given time there might be two regular staff employees and or two contractors. I have been here for three years which is an unusually long time to perpetuate a State contract. One of the things I became involved with was to help create some standards that were to be used by all developers. We all now conform to the standards that were documented about 2 years ago, but there are 35 databases with hundreds of objects all intertwined with other with the various back-ends so at this point the only way to change existing would be to redevelop the system entirely. So yes, we do use the basic Lezynski naming convention, but lots of existing stuff does not conform so we just live with it.

    Carla

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Auto Indexing (Access 2002)

    I have my autoindexing turned off (set the field to blank), and I recommend everyone do the same. The primary reason I do it is do keep Access from creating duplicate indexes! When you create a relationship between 2 tables and enforce referential integrity, Access creates a hidden index on the child table based on the field in the relationship. If you have autoindex set, it may create an additional index on this field.

    Example: You have tblCust (PK: CustID) as the parent to tblOrders, based on the CustID field. When you create tblOrders and include the CustID field, autoindex will create an index. When you then define the relationship between the 2 tables (with enforced RI), Access creates another (hidden) index on the same CustID field. Not only is this inefficient, but as there is a 32 index/relationship limit on any table, that is 1 less index and/or relationship you can define if you really need to.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Indexing (Access 2002)

    Thank you for the additional information. I downloaded your documentor utility to take a look at the visible indexes in some of the databases I am working with and there are way too many visible indexes, so I would have to think there are many hidden ones as well. I did not know there was a limit on relationships/index limit. Max I see on anything I am currently working on is 23. This is good information because the databases I am working with continue to grow, and are probably going to continue to be used for a few more years.

    Carla

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Auto Indexing (Access 2002)

    My utility shows the hidden index, they are the ones with a check in the "Foreign" column.

    What I'll do is run the utility so I just return the indexes. Then I'll check each table to look for (and delete) any configured indexes that match a hidden index.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #11
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Indexing (Access 2002)

    Mark,

    Are there any consequences to deleting the hidden indexes? I saw the check boxes for foreign for the indexes and did not know what they were. I am about to start to use your utiltity on some of ourdatabases to identify the indexes and remove the ones I feel are not necessary. A couple of the databases are sluggish opening some of the forms so I have started to look for ways to optimize them. I have done some things that have helped and am thinking this might bring some slight improvement. Agani thank you for the additional information.

    Carla

  12. #12
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Auto Indexing (Access 2002)

    You can't delete the hidden indexes. These are used by Access to enforce RI. But you can safely delete the index that you (or the AutoIndex) created.

    For example, you might see 2 indexes on field CustID for tblOrders. One is marked as foreign, and probably has a name like tblOrderstblCust. the other one just has a name of CustID. Just open your database and open tblOrders in design mode. Then just delete the index on the CustID field.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  13. #13
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Indexing (Access 2002)

    I just ran into the same problem on an A97 database. Created a new table and tried to enforce RI on an existing table. It failed with a "too many indexes" error message. Searched the Lounge and found this thread. So I downloaded Mark's utility. I deleted one of the user-defined, duplicate indexes, then applied RI successfully, so there clearly was an issue about number of indexes.

    But after deleting a total of 5 duplicated indexes, I ran the utility again and found there were 15 existing indexes, including the hidden ones. 15 + 5 = 20 Why would Access run into the index limitation with 20 indexes instead of the 32 that are allowed in the table specification?
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  14. #14
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Auto Indexing (Access 2002)

    It is not only the number of indexes on the table, you have to add to that the number of relationships for which this table is the parent.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •