"Too many indexes on table" error when creating relationships in Microsoft Access 2010.
- by avianattackarmada
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.
