Too Many Indexes Bug

  • Follow


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/community/en-us/default.mspx?mid=d572e6b2-6f22-45f8-8558-5171b2111b0a&dg=microsoft.public.access
0
Reply Utf 4/16/2010 3:55:01 PM

"Secret Geek" <SecretGeek@discussions.microsoft.com> wrote in message 
news:D572E6B2-6F22-45F8-8558-5171B2111B0A@microsoft.com...
>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.

-- 
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

0
Reply Dirk 4/16/2010 5:29:27 PM


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.

"Dirk Goldgar" wrote:

> "Secret Geek" <SecretGeek@discussions.microsoft.com> wrote in message 
> news:D572E6B2-6F22-45F8-8558-5171B2111B0A@microsoft.com...
> >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.
> 
> -- 
> Dirk Goldgar, MS Access MVP
> Access tips: www.datagnostics.com/tips.html
> 
> (please reply to the newsgroup)
> 
0
Reply Utf 4/16/2010 6:02:01 PM

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.

"Secret Geek" wrote:

> 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.
> 
> "Dirk Goldgar" wrote:
> 
> > "Secret Geek" <SecretGeek@discussions.microsoft.com> wrote in message 
> > news:D572E6B2-6F22-45F8-8558-5171B2111B0A@microsoft.com...
> > >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.
> > 
> > -- 
> > Dirk Goldgar, MS Access MVP
> > Access tips: www.datagnostics.com/tips.html
> > 
> > (please reply to the newsgroup)
> > 
0
Reply Utf 4/17/2010 12:47:01 AM

On Fri, 16 Apr 2010 17:47:01 -0700, Secret Geek
<SecretGeek@discussions.microsoft.com> wrote:

>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...
-- 

             John W. Vinson [MVP]
0
Reply John 4/17/2010 1:34:16 AM

Secret Geek <SecretGeek@discussions.microsoft.com> wrote:

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/
0
Reply Tony 4/18/2010 12:45:53 AM

5 Replies
270 Views

(page loaded in 0.38 seconds)


Reply: