How many index can I have

[Guest]

Hi, can anyone tell me how many index can my access database have, and what
is the advantage & disadvantabe of having them? Thanks very much.

 

[Rick Brandt]

Hi, can anyone tell me how many index can my access database have,
and what is the advantage & disadvantabe of having them? Thanks very
much.

32 per table is the maximum.

Pros:
Queries are faster

Cons:
Bigger file
Slower updates

To put the Pros and Cons in proper perspective though imagine that I wrote
the Pros in letters about a foot tall and the Cons in letters about a tenth
of a inch tall.

 

[Brendan Reynolds]

You can have 32 indexes per table. See the following URL, and click the
'Table' link on that page, for details. Or search your local help file for
'Specifications'.

http://office.microsoft.com/assistance/hfws.aspx?AssetID=HP051868081033

Indexes speed up data retrieval, but can slow down updates (as the index
must be updated as well as the table). Consider adding indexes to fields
that are frequently used in joins or to filter or sort records.

 

[Guest]

As others have mentioned 32 is the maximum per table.

I usually index any field used in the criteria of a query unless the table
will never have many records. Say less than 500 records. A full table scan is
probably more efficient.

Watch out for Access creating duplicate indexes. Lets say that you make your
EmployeeID field the primary key of theEmployee table. Access creates an
index to enforce the primary key constraint. However Access has already
created yet another index on that field as Access automatically indexes an
field with the following characters anywhere in the field name: ID, key,
code, or num. For example "codependent" or "Hide" would have an index. You
can change this behavior by going to Tools, Options, Tables/Queries tab.