Too many indexes?

[Larry]

Ok, I have a problem I have never had in 13 years of working with MS Access.

I have a Personnel table, with 4 child tables (location information,
compliance information, visa information, validation information for each
person) which are all 1 to many relationships. The personnel table has a one
field primary key.

I also have 25 reference tables (code, description) 21 of which have a many
to one relationship to the main Personnel table. All of these tables are
linked, via the Lookup Wizard, to allow combobox usage in forms and such.

I have used the Relationship tool to define all the relationships between
the tables in the database, enforcing RI with cascaded deletes and such
where appropriate.

I just tried to create another relationship for a new table to the main
table, and got the message that an "index cannot be created b/c there are
too many indexes on the Personnel table". Help says you can have 32 (which I
never really thought I'd EVER approach).

I have not explicitly created any indexes on the Personnel table, other than
the PK of course. But, when I search through the columns, I find 7 of them
(other than the PK) have "Yes (No Duplicates)" for the index property. When
I look at the Index tool, for the table, sure enough, there are 9 indexes.
That's the 7 additional fields, and index on the PK field and the PK
definition itself.

So, with all this information, here are my questions:
1) Where did the 7 indexes come from? Some are based on the reference
tables, some aren't, and I did not define them intentionally.
2) The field PersonnelID has and index AND shows up as the PK, in the
primary tool. Can I remove the "Yes (No Duplicates)" or does this really
need 2 indexes since it's the PK?
3) I assume creating the relationships to the table, in the relationship
tool, is creating an index. Is this true and can I do anything about it?

I have thought about splitting the table up into many smaller tables, but
don't really want to do that b/c the user likes the current user interface,
and splitting the able up now would mean more subforms in the UI and more
hassle for the user (since tabs don't work very easily to move between
fields with subforms).

I appreciate any thoughts and suggestions.

Larry

 

[Kevin @ 3NF]

I also have 25 reference tables (code, description) 21 of which have a
many

to one relationship to the main Personnel table. All of these tables are
linked, via the Lookup Wizard, to allow combobox usage in forms and such.

The lookup wizard creates indexes behind the scenes. Using this feature is
generally discouraged for this very reason, among others.

 

[Larry]

Interesting, I've always recommended it's use, though I have never had 24
reference tables on one table before.

Now I just have to figure out how to get rid of those indexes, since they do
not show up in the index tool!

 

[Kevin @ 3NF]

Changing the relevant field from a lookup field to a simple number field
(relating back to the numeric ID field in the other table) should do it, but
you may have to copy the table structure into a new table to clean it up.
Possibly a compact and repair as well.

 

[Larry]

Yeah, I went through and changed them all back to textboxes, then did a
compact and repair. I guess I won't be able to tell if it got rid of those
indexes though, unless I try to add several more, since they didn't show up
in the index tool

Seems like an index on a table should ALWAYS show up in the index tool!

 

[Allen Browne]

You can see the indexes via DAO:

dim db as DAO.Database
Dim tdf as TableDef
Dim idx as DAO.Index

Set db = dbEngine(0)(0)
Set tdf = db.TableDefs("MyTable")
For Each idx In tdf.Indexes
Debug.Print idx.Name
debug.print, "Field(s): " & idx.Fields
If idx.Clustered Then debug.print, "Clustered"
If idx.Primary Then debug.print, "Primary"
If idx.Foreign Then debug.print, "Foreign"
If idx.Required Then debug.print, "Required"
If idx.IgnoreNulls Then debug.Print, "Ignore Nulls"
debug.print, "Distinct Count = " & idx.DistinctCount
Debug.Print
Next
Set tdf = Nothing
Set db = Nothing

 

[Larry]

Thanks Allen!

Showed I now have 23 indexes on the table, 21 reference tables (via
relationship tool) and 2 on the PK.

Still a lot of indexes, but at least it's not 32!