The following is copied from a thread here at tek-tips, sorry I don't know which thread, but maybe it holds a clue for you:
Why should an AutoNumber field NOT be used as a primary key
There is a good reason why autokeys shouldn't be used as primary keys in access but what this guy said doesn't make any sense. Access will always create a new (unique)key every time a record is inserted. Access also does not allow autokeys to be manually created by users or code. The problem is when many users are inserting into the same table at once. There is the unlikely, but possible, chance that two users will try to insert a new record at the same time and because Jet SQL creates autokeys on the front end first, a duplicate key could get inserted into the table. Large scale applications use a form of record locking to prevent this but for small applications you don't really need to worry about this happening. Hope this clears things up. You can further prevent duplicates by using more than one primary key, usually called combined keys.
I usually don't use autonumbers as primary keys because, as the "aside" statement indicates, there usually is nothing to prevent a duplicate record from being entered in an autonumber as primary key situation. Since autonumber generates a new number every time a new record is entered, the primary key has nothing to check against previous records that were entered to see if there is duplicate information.
On the other hand, for example, if you use something like a social security number as a primary key, you would not be generating a new number at the time the record is entered, as autonumber does. If you are trying to enter a SS# that has already been entered for an employee, the PK should catch it and return an error message, and you will not end up with a duplicate record for that employee. And, of course, you should never have more than one SS# for any one employee. That's what makes it a good primary key candidate.
There are instances where I use autonumber, for example when I have a table of transactions, (say, financial transactions, for example, which can often contain duplicate information) and I simply need a field to act as a control number when I am using code to track user input.
Hope this helped somewhat.
Consider the following table and data
Table
Field Type
ID Autonumber Primary Key
ProductID Text (10)
Product Text (50)
Price Currency
Data
ID ProductID Product Price
1 W001 Widgets $0.05
2 D002 DooHickeys $0.20
3 G001 Gadgets $1.00
4 W001 Widgets $0.05
The data is complying with the table settings, but you can still duplicate data. If however, the Primary Key is set to the ProductID field, the duplicate data will result in an error message advising of duplicate primary keys.
Of course, there are times when using the Autonumber field as the Primary key doesn't matter. These tables are quite often tables on the MANY side of a One-to-Many relationship, where the PK is only there to speed up sorting and querying and the main identifier is a Foreign Key..
