Duplicate Indexes

Duplicate Indexes

By default, Access indexes certain fields for you automatically, based on the name that you call the field. By default, this happens if the field has 'ID', 'code' or 'num' in the field name, then Access creates an index. This can cause a duplication of indexes if the field is also the Primary Key for the table, having one called PrimaryKey, and the other called the name of the field. Although it is a very good idea to have indexes on fields in the table, as this speeds the retrieval of data, having too many indexes will slow the deletion and creation of records.

To prevent Access from creating these indexes automatically, from the database window, select Tools|Options, and then the Tables/Queries tab. Remove the text from the field labelled 'AutoIndex on Import/Create'. Note that this is an application wide setting, so once you have set this once in an Access database, you won't need to change it again.

To remove any existing duplicate indexes, you will need to open each table in design mode, and then select Indexes (View|Indexes). If there is a duplicate index on a field, then remove it by right-clicking on the duplicate row, and selecting 'Delete Row'.

Top

Last modified at 06/06/2006 15:00:30