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

Question

I have tblUsers that has a UserID primary key.

UserID is used as a foreign key in many tables. Inside the table, it is used as a foreign key for several fields (for example, ObserverID, RecorderID, CheckerID).

I have successfully added relationships (in the MS Access "Access" view), where I have table aliases to execute multiple relationships for each table:

* tblUser.UserID → 1 for many → tblResight.ObserverID

* tblUser_1.UserID → 1 for many → tblResight.CheckerID

After creating about 25 relationships with referential integrity binding, when I try to add an extra, I get the following error:

"The operation failed. There are too many indexes in the tblUsers table. Delete some of the indexes in the table and retry the operation."

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

Am I using the wrong relationship interface? Does it have access to create an index to ensure referential integrity anytime I create relationships (especially indexes that won't appear when the script runs)? I am a little puzzled, any help would be appreciated.

Answer 1

Well, after several studies, I think I got the answer to this question. This seems to be a very common access ceiling. I will summarize this post , which I found below:

Each table can contain only 32 'restrictions. Each index and forced use of referential integrity (RI) are calculated in accordance with this 32. MS Access automatically creates a restriction when you choose to force RI; You cannot disable this option.

All the code jokes and things I found through google returned that I had six indexes in the table (and therefore I was confused). What I did not find / did not know was that my 25 relationship was counted against my 32, because I used RI.

My solution was to drop RI into the “lower priority” fields (it hurts me to say this) and “force” it through the data entry forms.

In principle, this is another reason why I quickly transfer access to PostgreSQL.

If anyone has a good job, I would like to be here. Thanks.

Answer 2

Your table has hidden indexes that were created when you defined your relationship. Hidden index names begin 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 can make this ListIndexes () function include hidden indexes by changing this line to this:

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

In addition, you can check the total number of indexes for your table using this statement in the Immediate window:

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

Answer 3

You can get a list of all indexes, including hidden ones, with the following:

 Sub TableListIndexes(sTableName As String, Optional bPrintFields As Boolean = False) 'Print indexes on a table, and fields in each index. 'Need to add a reference to Microsoft ADO Ext. [version] for DDL and Security (ADOX). Dim cat As New ADOX.Catalog Dim idxs As ADOX.Indexes Dim idx As ADOX.Index Dim col As ADOX.Column Dim i As Integer Set cat.ActiveConnection = CurrentProject.Connection Set idxs = cat.Tables(sTableName).Indexes For Each idx In idxs Debug.Print i, idx.Name If bPrintFields Then For Each col In idx.Columns Debug.Print , col Next End If i = i + 1 Next End Sub Sub TestTableListIndexes() TableListIndexes "tblProject" End Sub 

What gives

 0 PrimaryKey 1 ProjectBusinessUnitID_6D55FF7827CC48648A15A8E576EF02EF 2 ProjectDivisionID_9CAC7B9D8136467B97F9BAA7217EAC38 etc 

Note that if you have multiple-valued fields in the table, each will have a hidden index.

Answer 4

This is pretty old, but the problem arises very often, and this thread comes first in search engines (someone told me;))

A good way to overcome this problem is to work with a "helper table" to link to other tables.

Example: A table entry is associated with many other tables for various reasons. She may also need many foreign keys for herself. Such a table is often obtained from possible indices. I also have three or four of them in my largest projects.

To almost double the possible matches / indexes of RI, you can work with an auxiliary table that has a 1: 1 RI. Join the tblArticle table with only a unique identifier as a field. I call it the same as with the shortletter fk in front of it, as usual. Let me call it tblArticleLinker.

Each table that receives a foreign key from tblArticle, for example Order-Position, gets its connection from tblArticleLinker. -> You do not lose the index for all these links, only one for Linkertable

The only thing you need to make sure is that you always add the key to linkertable when saving, otherwise it is impossible to use a record.

Such a table - in my experience - is much easier to process than the usual approach to splitting fields in different tables. In queries, you don’t particularly need a helper (sometimes queries are faster if you do this), you can directly refer to the table. This is simply not done automatically, as usual.

Tipp: the same approach can be used so that the user can only use the “released” records. Or just use as a hard filter. This helps overcome possible Software-Bugs errors that do not match the logic that they should follow.