Too Many Indexes Error When creating Relationship

[rdpress]

I posted in Tables & Relationship Forum some time ago with no response.. Sorry for the duplicate entry -- but I'm confused -- please help

When trying to setup a one-to-many relationship with referential integrity, I get the following error Message:

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

The ‘Help’ button has the following message:

**********************************************************
The operation failed. There are too many indexes on table <name>. Delete some of the indexes on the table and try the operation again. (Error 3626)

A table in a Microsoft Jet database can have no more than 32 indexes. You cannot create additional indexes on a table with this many indexes. Further, you cannot compact a database containing a table with this many indexes because compacting a database involves creating several new indexes.
Delete one or more indexes from the named table and try the operation again.
************************************************************


I tried to compact the database and it compacted fine – which surprised me because the help section said it shouldn’t compact.

When I list the indexes through the documenter, there are 13 listed. The table specs say I can have 32.

I do have several one-to-many relationships set on this table, is there a limit to relationships with referntial integrity? Do these type of releationships create an index that goes won't appear on index listing from documenter?I can’t find one listed in the specifications.

Any ideas, suggestions greatly appreciated!!!

 

[Lightning]

Yes, for some reason Access counts Indexes and relationships together when calculating the number of indexes on a table. I ran into this last year when I was creating a main table with about 40 linked sub tables.

My solution was to create two separate &quot;Main&quot; tables and create a relationship between them, and then link half the sub-tables to each &quot;main&quot; table.

The limit is mentioned in the help file, but it is under something that has nothing to do with relationships. Unfortunately I can't remember the topic heading. However,If you work with a limit of 32 indexes AND relationships per table, you should be OK.

HTH
Lightning