I'm wondering if adding an autonumber primary key will improve the
performance of a multiuser access database on a network share. I have
a website that lists many tips for improving performance of access, but
doesn't mention primary keys.
However, it seems logical to think that having no primary key means
that when a user updates a record, the database has to do comparisons
on multiple fields to identify the specific record being updated. With
a primary key, the database can index into that primary key of the
record being updated much quicker.
Does this make sense?
If not, I'm wondering, if I update a field in a record via a form, how
does the database identify quickly which record is being updated and
write that update. Does it generate some sort of hidden index on it's
own that is used for this purpose?
I could create an index on a text field that contains very few
duplicates, but does that index only get used for queries that use it
in the where clause?
The most notable performance problem is users flipping between records
on a form after making updates to the record. The form is based on a
query that takes the user's initials and pulls all records that have
those initials in a partiulcar field. So each user has a mutually
exclusive set of records they are working on, but the records are all
from the same table, and each suer has 200-1000 records at a time.