Too Many Indexes: 16?

[PeteCresswell]

I've got sixteen indexes on a table - as counted in Table Design |
View | Indexes.

One is a compound index, so maybe call it 17.


But when I open up Relationships and try to add a recursive
relationship between two indexed fields in this table, MS Access says

 

[Dirk Goldgar]

I've got sixteen indexes on a table - as counted in Table Design |
View | Indexes.

One is a compound index, so maybe call it 17.


But when I open up Relationships and try to add a recursive
relationship between two indexed fields in this table, MS Access says
-----------------------------------------------------------
"The operation failed.
There are too many indexes on table 'tblSecurity'.
Delete some of the indexes on the table and try the operation again."
-----------------------------------------------------------

I had thought 32 indexes were allowed per table.

Or does adding a recursive relationship somehow double them up?

As I understand it, each enforced relationship adds another, hidden, index
all its own, so if your table is related to lots of other tables you could
get this error with only a few indexes actually shown in the Indexes dialog.

 

[PeteCresswell]

As I understand it, each enforced relationship adds another, hidden, index

all its own, so if your table is related to lots of other tables you could
get this error with only a few indexes actually shown in the Indexes dialog.

That seems tb the case. I took referential integrity enforcement off
of two
other indexes and it let me add the recursive one.\

Seems kind of weak to me..... A dozen lookup tables isn't that many.

 

[Tony Toews [MVP]]

But when I open up Relationships and try to add a recursive
relationship between two indexed fields in this table, MS Access says

I have a tool which shows duplicate indexes. However it isn't quite
complete and good enough for every to download.

Email me and I'll send it your way.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[PeteCresswell]

Email me and I'll send it your way.

I emailed sometime last week. Didn't get a bounce message... but no
reply.

 

[Tony Toews [MVP]]

I emailed sometime last week. Didn't get a bounce message... but no
reply.

My apologies. Just sent now.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/