Too many indexes warning on table import

[R. Choate]

I have a table that I inherited which has way too many fields (I'll fix that a bit later), but it also had quite a few indexes. I do
not believe it had as many as 32 (the limit for Access2K/XP). I removed all but one of the indexes (an auto number field) and saved
the table. Then I tried to import that table into another database. I got the warning noted above. I had to hit the enter or escape
key 33 times before it would bring the table over. Then everything looks fine. Please tell me what is going on. I have to get this
fixed and I don't understand why I'm getting that warning.

 

[John W. Vinson/MVP]

I have a table that I inherited which has way too many fields (I'll fix that a bit later), but it also had quite a few indexes. I do
not believe it had as many as 32 (the limit for Access2K/XP). I removed all but one of the indexes (an auto number field) and saved
the table. Then I tried to import that table into another database. I got the warning noted above. I had to hit the enter or escape
key 33 times before it would bring the table over. Then everything looks fine. Please tell me what is going on. I have to get this
fixed and I don't understand why I'm getting that warning.

Access will create extra indexes for at least two possible reasons:

By default, any field whose name ends in "ID" or "num" or a couple of
other strings will be indexed. This utterly stupid rule can be removed
by selecting Tools... Options... Tables/Queries and clearing the
contents of the "Autoindex..." textbox.

It's less easy to resolve the other cause: any field involved in a
defined Relationship with referential integrity enabled will be
automatically indexed. This index is essential, since it's the
mechanism by which RI is enforced. If you have 32 lookup fields in the
table... there are your 32 indexes!

 

[R. Choate]

Hi John,

I haven't visited with you for a long, long time. Very good to see you are still here helping people.

That is very helpful information. I will try that first thing immediately and hope it works. I can't imagine the 2nd one being in
play here because, get this, the guy built this database with only one stupid table! There shouldn't be any relationships at all.
You did mention lookup fields though. That scares me because the moron might have tried that. I've always been advised to avoid
those like the plague because of the multitude of inherent problems. Sound like you just listed one of them. I'm crossing my fingers
he didn't try setting any lookup fields. Can you have lookup fields with only one table?

Thanks, and good to hear from you again!

Richard

--
RMC,CPA

I have a table that I inherited which has way too many fields (I'll fix that a bit later), but it also had quite a few indexes. I
do
not believe it had as many as 32 (the limit for Access2K/XP). I removed all but one of the indexes (an auto number field) and saved
the table. Then I tried to import that table into another database. I got the warning noted above. I had to hit the enter or escape
key 33 times before it would bring the table over. Then everything looks fine. Please tell me what is going on. I have to get this
fixed and I don't understand why I'm getting that warning.

Access will create extra indexes for at least two possible reasons:

By default, any field whose name ends in "ID" or "num" or a couple of
other strings will be indexed. This utterly stupid rule can be removed
by selecting Tools... Options... Tables/Queries and clearing the
contents of the "Autoindex..." textbox.

It's less easy to resolve the other cause: any field involved in a
defined Relationship with referential integrity enabled will be
automatically indexed. This index is essential, since it's the
mechanism by which RI is enforced. If you have 32 lookup fields in the
table... there are your 32 indexes!

 

[Pete D.]

Must compact and repair after deleting keys to really get rid of them.

 

[R. Choate]

I believe your suggestion and the other suggestion to do a compact and repair solved my annoying problem. Thank you very much.

Richard

--
RMC,CPA

I have a table that I inherited which has way too many fields (I'll fix that a bit later), but it also had quite a few indexes. I
do
not believe it had as many as 32 (the limit for Access2K/XP). I removed all but one of the indexes (an auto number field) and saved
the table. Then I tried to import that table into another database. I got the warning noted above. I had to hit the enter or escape
key 33 times before it would bring the table over. Then everything looks fine. Please tell me what is going on. I have to get this
fixed and I don't understand why I'm getting that warning.

Access will create extra indexes for at least two possible reasons:

By default, any field whose name ends in "ID" or "num" or a couple of
other strings will be indexed. This utterly stupid rule can be removed
by selecting Tools... Options... Tables/Queries and clearing the
contents of the "Autoindex..." textbox.

It's less easy to resolve the other cause: any field involved in a
defined Relationship with referential integrity enabled will be
automatically indexed. This index is essential, since it's the
mechanism by which RI is enforced. If you have 32 lookup fields in the
table... there are your 32 indexes!

 

[John W. Vinson/MVP]

I believe your suggestion and the other suggestion to do a compact and repair solved my annoying problem. Thank you very much.

Good news. And thanks for the kind words... welcome back to the
newsgroups, good to see you!