too many indexes

[Kate]

I have read on various websites that Access 2000 tables
are limited to 32 indexes per table. I have only made 24
relationships (when I "show direct" relations) for my main
table. When I try to make another, it does not allow me
to and gives me an error message saying "too many indexes".

Does anyone have any solutions to this?

 

[Allen Browne]

When you create a relationship with referential integrity, Access
automatically creates the index it needs to enforce that, but there are
several other ways that indexes get created as well.

Examples:
1. A primary key is an index.

2. In the lower pane beneath a field in table design, is an Indexed
property. The settings "Yes (No Duplicates)" or "Yes (Duplicates ok)" both
generate an index.

3. Any entries under:
Tools | Options | Tables/Queries | Autoindex on create/import
cause Access to automatically create indexes on fields that have a name
ending in ID, Num, Code, etc. If you understand indexes, you almost
certainly want to delete these. For example, if you make a field named
"CustomerID" and mark it as primary key, you get two indexes: one that
Access automatically creates (because if the "ID" suffix), and one because
you made it primary key.

4. If you use the Lookup wizard (Data type column in table design), it also
creates an index. Since the field almost always has a relation, this is also
undesirable. The solution is not to use this wizard.

In addition, don't index your foreign key fields. As the opening sentence
above points out, they are automatically indexed when you create the
relation with RI, so setting their Indexed property as well duplicates the
index.