How to remove duplicate indexes

[Brian]

I just turned off auto-index & now need to delete the duplicate indexes it
caused. How?

 

[Brian]

Never mind. I was looking at the Indexed property of the field in table
design, not the Indexes window, which is where I needed to be.

 

[Allen Browne]

Actually this is quite a messy job.

It has to be handled programmatically, since many of the duplicated indexes
are hidden. The indexes involved in maintaining the foreign keys need to be
kept, and they might not be the ones with the name you want (i.e. Access
uses a GUID if the index name is already taken.)

I started work on a utility to do this back in the Access 97 days, but never
took it past quick'n'dirty. The interface shows the duplicated indexes,
recommends one to delete, and allows you to perform the delete, instead of
actually changing your structure without your approval.

If you're interested, you can download the example from:
http://allenbrowne.com/CleanUpIndexes.zip
It should convert to a later version of Access without problem.

 

[Brian]

Thanks, Allen. I have had to programmatically create indexes on occasion, but
I have never had to think about cleanup like this.

As I discovered after my initial post, the Index window in table design does
warn on attempts to delete an index that is involved in a relationship, so I
can simply delete the relationship, remove the superfluous index, and
re-create the relationship. Is this leaving something else orphaned out
there, though?

 

[Allen Browne]

There are 2 causes for duplicated indexes in Access:

1. The setting under:
Tools | Options | Tables/Queries | AutoIndex on ...
I think that's the one you were referring to, where if you give a field a
name ending with "ID", "num", "code", etc, then Access automatically creates
an index, and then if you mark it as primary key, you have a duplicate
index.

2. Relations with RI.
Access creates hidden indexes to manage relationships where you asked for
enforced Referential Integrity. So if you index your foreign key fields and
then create relations with RI, you also have duplicate indexes. You can
avoid these by not manually indexing your foreign keys.

The function below lists relations, including the hidden ones.

Function ShowRel()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

Set db = CurrentDb()
For Each rel In db.Relations
Debug.Print rel.Name, rel.Table, rel.ForeignTable, rel.Attributes
For Each fld In rel.Fields
Debug.Print , fld.Name, fld.ForeignName
Next
Next

Set fld = Nothing
Set rel = Nothing
Set db = Nothing
End Function

 

[Brian]

Thanks for the clarification. I indeed have duplicates for both reasons. I
got #1 turned off & cleaned up, & now I can clean up #2.