Primary Key and Other Indexes

[tbl]

When a new, simple table is created in Access, with a
typical Primary Key assigned, I notice that two Indexes are
listed in the "Indexes" listing dialog. One is for the
Primary Key, and the other is for the same field, but not
inicating anything about a Primary Key.

Are both required? Beneficial?

 

[John Vinson]

When a new, simple table is created in Access, with a
typical Primary Key assigned, I notice that two Indexes are
listed in the "Indexes" listing dialog. One is for the
Primary Key, and the other is for the same field, but not
inicating anything about a Primary Key.

Are both required? Beneficial?

No, and no. Access has a somewhat infuriating misfeature: if you
select Tools... Options... Tables/Queries, you'll see a textbox
labeled "Auto Index on import/create". This will automatically index
fieldnames ending in the listed text strings (ID, Num, ...) - EVEN IF
an index already exists for that field.

I'd suggest clearing this textbox in all databases. If I want an index
- I'll create an index!

John W. Vinson[MVP]

 

[tbl]

Correction: The duplicate index gets created based on a setting in
Tools>Options>Tables/Queries - see the AutoIndex option. If there are entries
there, and your intended key field matches one of them (and it doesn't have
to be an exact match!), two indexes may get created: non-primary as a result
of the "Autoindex on create" option, and primary as a result of clicking Yes
on the primary key dialog. My preference is to turn off autoindexing and
manage indexes by hand.

Thank you Ted!

 

[tbl]

No, and no. Access has a somewhat infuriating misfeature: if you
select Tools... Options... Tables/Queries, you'll see a textbox
labeled "Auto Index on import/create". This will automatically index
fieldnames ending in the listed text strings (ID, Num, ...) - EVEN IF
an index already exists for that field.

I'd suggest clearing this textbox in all databases. If I want an index
- I'll create an index!

John W. Vinson[MVP]

Thanks John! Good stuff.