"Too many indexes on table" error when creating relationships in Microsoft Access 2010

Question

I have tblUsers which has a primary key of UserID.

UserID is used as a foreign key in many tables. Within a table, it is used as a foreign key for multiple fields (e.g. ObserverID, RecorderID, CheckerID).

I have successfully added relationships (with in the the MS Access 'Relationship' view), where I have table aliases to do the multiple relationships per table:

*tblUser.UserID -> 1 to many -> tblResight.ObserverID

*tblUser_1.UserID -> 1 to many -> tblResight.CheckerID

After creating about 25 relationships with enforcement of referential integrity, when I try to add an additional one, I get the following error:

"The operation failed. There are too many indexes on table 'tblUsers.' Delete some of the indexes on the table and try the operation again."

I ran the code I found here and it returned that I have 6 indexes on tblUsers. I know there is a limit of 32 indexes per table.

Am I using the relationship GUI wrong? Does access create an index for the enforcement of referential integrity any time I create a relationship (especially indexes that wouldn't turn up when I ran the script)? I'm kind of baffled, any help would be appreciated.

Answer 1

Okay, after doing some more research, I think I got the answer to this question. Apparently this is a very common ceiling with access. I'll sum up this post I found below:

Each table can only have 32 'constraints'. Each index and enforcement of referential integrity (RI) counts towards this 32. MS Access automatically creates a constraint when you select to enforce RI; you cannot disable this option.

All the code snipets and things I found through google, returned that I had six indexes on the table (and hence I was getting confused). What I wasn't finding/didn't know was that my 25 relationships were counted against my 32, because I had RI enforced.

My solution to this was to drop RI on the 'lower priority' fields (it pains me to say that), and to 'enforce' it through the data entry forms.

Basically, this is one more reason I'm migrating out access and into PostgreSQL shortly.

If anyone has a better work around, I would love to here it. Thanks.

Answer 2

Your table has hidden indexes which were created when you defined your relationships. The names for hidden indexes start with the "~" character. But the code you found ignores hidden indexes because of this expression:

If Left(tbl.Name, 4) <> "MSys" And Left(tbl.Name, 1) <> "~" Then

You could make that ListIndexes() function include hidden indexes by changing that line to this:

If Left(tbl.Name, 4) <> "MSys" Then

Also, you can verify the total number of indexes for your table with this statement in the Immediate Window:

? CurrentDb.TableDefs("tblUsers").Indexes.Count