Too Many Indexes Bug

[Secret Geek]

I have encountered, what as far as I am concerned, must be bug/fault in
ACCESS 2007 (may have existed in previous versions. The bug does not produce
a fault report so I can't do it automatically through wiondows error
reporting and I use enterprise edition so can't use the normal technical hep
route for reporting a problem.

The bug is that I have 5 indexes created by me on a table (1 for the primary
key and 4 other unique indexes to enforce uniqueness or to allow RI
enforcement to child tables.

ACCESS has created a further 12 Foreign Key indexes that I can see in the
database documenter report and I get the same if I use DAO to list the
indexes (indicies?). That makes 17 in total.

The limit is supposed to be 32 indexes yet when I try to create another
index it fails saying there are too many indexes. Also when I try to create
another enforced RI relationship to another child table using an existing
unique index (already used to enforce RI to another table), it also fails
saying there are too many indexes but it shouldn't even be trying to create
another index.

I have also tried removing foreign key constraint, confirmed the FK index
was removed, created my enforced RI relationship using the existing index
mentioned previously but then when I try to put the deleted FK relationship
back it still fails saying too many indexes.

I have also tried compact/repair and import and export to another database
file.

The only thing I haven't tried yet is to export the table without any
indexes, import it back again, recreate the indexes to the new copy of the
table and recreate all of the relationships.



----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...-8558-5171b2111b0a&dg=microsoft.public.access

 

[Dirk Goldgar]

I have encountered, what as far as I am concerned, must be bug/fault in
ACCESS 2007 (may have existed in previous versions. The bug does not
produce
a fault report so I can't do it automatically through wiondows error
reporting and I use enterprise edition so can't use the normal technical
hep
route for reporting a problem.

The bug is that I have 5 indexes created by me on a table (1 for the
primary
key and 4 other unique indexes to enforce uniqueness or to allow RI
enforcement to child tables.

ACCESS has created a further 12 Foreign Key indexes that I can see in the
database documenter report and I get the same if I use DAO to list the
indexes (indicies?). That makes 17 in total.

The limit is supposed to be 32 indexes yet when I try to create another
index it fails saying there are too many indexes. Also when I try to
create
another enforced RI relationship to another child table using an existing
unique index (already used to enforce RI to another table), it also fails
saying there are too many indexes but it shouldn't even be trying to
create
another index.

I have also tried removing foreign key constraint, confirmed the FK index
was removed, created my enforced RI relationship using the existing index
mentioned previously but then when I try to put the deleted FK
relationship
back it still fails saying too many indexes.

I have also tried compact/repair and import and export to another database
file.

The only thing I haven't tried yet is to export the table without any
indexes, import it back again, recreate the indexes to the new copy of the
table and recreate all of the relationships.

Does your table use lookup fields? These create hidden relationships and
indexes to support them, so you might be surprised how many indexes are
actually set on the table, many of which may be redundant. Have you already
taken these into account? You said you checked the indexes using DAO? What
do you get when you enter (in the Immediate window),

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

Also when I try to create
another enforced RI relationship to another child table using an existing
unique index (already used to enforce RI to another table), it also fails
saying there are too many indexes but it shouldn't even be trying to
create
another index.

I'm not sure about this. How are you creating this relationship? If you do
it in the Relationships window, I think Access will create a new index even
if a suitable one already exists.

I'm not saying you're wrong about a bug -- you sound like you know what
you're doing -- but I want to make sure the obvious possibilities are
eliminated first.

 

[Secret Geek]

Dirk,
I hadn't considered the idea that having lookup fields may create truly
hidden indexes. By truly hidden, I mean FK indexes don't appear in the index
window but can been seen when creating a report through the database
documenter wizard and also using VB with DAO code. If lookups do create truly
hidden indexes, that might explain the problem.

Also, I am creating relationships throught the relationship window and it
must at least create a temporary index otherwise creating the raltionship
which does (should) use an existing index wouldn't fail, but I assumed (and
still assume), that as you have to have an existing unique index, whatever it
does should only be temporary (possibly not but then I would argue that in
itself is a serious bug). I will try getting rid of some lookup fields as I
only created them to save me having to recode them on each form or forgetting
to code them.

 

[Secret Geek]

Damned sure I have got to the bottom of it. I have also managed to recreate
the problem on a brand new, 'clean' table.

Also, deleting lookups didn't work on ths table and other tables lookups
that reference this table didn't work either.

CONCLUSION...
I beleive ACCESS is incrementing a counter each time you create a unique
index or change an existing index to unique but under certain circumstances
doesn't decrement if/when you delete one of those indexes or change a unique
one to non-unique. As a consequence, once the counter reaches 32 you can no
only have foreign key or other unique indexes. Export/import doen't work as
it seem to take the counter field with the table definition.

To recreate...
Create a brand new table with 2 columns, doesn't matter what they are called
or what data type. Create a primary key on one column, then create 31 unique
indexes on the second column. Save it. Now change the 31 unique indexes to
non-unique and save it. Now try to change one of those indexes back to unique
and you get the too many indexes message. If you now delete all of the
indexes on that second column you can now create unique indexes on it again
so it's actually got something to do with how many unique indexes any given
column is involved in pehaps how many unique indexes a given column is the
fiorst index in. Whatever the exact combination is I don't know, i only know
it's a headache.

I got the problem enforcing a relationship using an index that already
existed and lincluded the primary key column and luckily for me solved it by
deleting another index that also included the primary key which then allowed
me to enforce the relationship and recreate a foreign key to the table which
didn't include the primary key column.

Possible solutions if I need another unique index which includes my primary
key might be to remove enforcement from all constraints that include my
primary key and remove all of the indexes that include my primary and then
start putting them back. if that didn't work I would try creating a new clean
table, copy/paste the columns, create the necessary user defined indexes,
delete the old table and replace it with this one recreating all of the
relationships. Now that I think I have a clearer understanding of the problem
I am now sure one of those would would work, but fortuntaely I don't have to
face that... yet.

 

[John W. Vinson]

CONCLUSION...
I beleive ACCESS is incrementing a counter each time you create a unique
index or change an existing index to unique but under certain circumstances
doesn't decrement if/when you delete one of those indexes or change a unique
one to non-unique. As a consequence, once the counter reaches 32 you can no
only have foreign key or other unique indexes. Export/import doen't work as
it seem to take the counter field with the table definition.

A similar problem happens with the 255 field limit: changing a field eats one
of the 255 slots.

This problem can be gotten around by compacting and repairing the database.
Does compacting recover your missing indexes?

It's a bug indeed, whether or not, but it might at least get around the
problem...

 

[Tony Toews [MVP]]

If you email me directly I have a database that will analyze the
indexes and show you which are duplicate so you can remove the extra
indexes. As it isn't completely polished I don't make it available
for download.

Email address is tony at granite dot ab dot ca.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/