TOO MANY INDICES IN RELATIONSHIPS

[Glint]

Hi All,
The database I use to manage membership has a Members table that has
relationships to almost every other table in the database. There have been
accidental deletion of duplicate records in the past; problem was that some
of such duplicates had transacted with other tables unknown to the user doing
the deletion. This caused problems later.
So I decided to enter all the relationships correctly to avoid accidental
deletion of useful data. The problem is that less than half way to relating
the Members table to other tables (50 of them), I get a message that the
indices are too many for the Members table.
How do I solve this problem?

 

[Allen Browne]

You can't. IME, this is the most common limit with JET tables.

There are some possible workarounds. You certainly want to get rid of any
non-crucial indexes on the table. For example, Access automatically indexes
fields with certain names, such as ID. So a foreign key named (say)
CategoryID is automatically indexed. Then you make it primary key, and you
now have 2 indexes on the one field. Similarly if you manually index a
foreign key field, and then create a relationship with referential
integrity, Access creates another hidden index on the field to manage the
RI. There may be some other indexes you can live without too.

After removing spurious indexes, the next possibility is to reduce the
number of related tables. Many database have lots of little lookup tables.
Each of these has just a couple of fields (e.g. an id number and text.) You
might be able to combine several of these lookup tables into one table with
a 3rd field for lookup type. This requires one relationship, rather than one
for each separate lookup table.

The next step is to decide which relations are less crucial, and can be
managed manually. This means writing code in the form where the lookup
table's data is exposed to the user. Cancel the Delete event if the code is
in use; block the form's BeforeUpdate event if the code that is in use was
changed, and so on.

 

[Tom van Stiphout]

On Fri, 20 Jun 2008 16:02:39 +0800, "Allen Browne"

That is only true if you have the nanny option "Auto Index on Create"
on in the Options dialog. It's on by default, but self-respecting
developers would want it off and be in full control of what indexes
are created. Still, 32 per table is the max.

-Tom.

There are some possible workarounds. You certainly want to get rid of any
non-crucial indexes on the table. For example, Access automatically indexes
fields with certain names, such as ID. So a foreign key named (say)
CategoryID is automatically indexed.

<clip>