AutoNumber for Primary Key

[MT DOJ Help Desk]

Access 2000

Is it possible--or wise--to use the AutoNumber data type for the primary
key? I seem to remember reading that it's not a good idea but I didn't
fully understand why. Can anyone explain this to me?

FYI: The tables in my current little database are using the Number data type
for the primary keys.

-- Tom

MT DOJ Help Desk

Making the world a safer place.

 

[Allen Browne]

There was a flaw in Access/JET where it would give duplicate autonumbers
under some circumstances. Microsoft did address this issue in the JET
service packs, so provided you have JET 4 SP8, you should be okay.

Some users are still wary after that experience, but we use AutoNumbers in
every project we write.

 

[Michael Cheng [MSFT]]

Hi,

Thanks Allen Browne's answer.

From your descriptions, I understood that you would like to know something
about Autonumber for your development. Have I understood you? If there
exists anything I misunderstood, please feel free to let me know

First of all, Autonumber is a unique sequential (incremented by 1) number
or random number assigned by Microsoft Access whenever a new record is
added to a table. AutoNumber fields can't be updated.

Secondly, although autonumber is easy for use, you should be careful
handling it. You should spend time making yourself familiar with how to
reset, delete it. IMO, whether you should use autonumber is based on your
project, sometime, transaction method to make increasement may be more
reliable.

For more detailed information about it, searching MSDN with keywords access
and autonumber, which, I believe, will give you more knowledge about it

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.

 

[MT DOJ Help Desk]

Thanks for the information. I'll make sure I have JET 4 SP8.

I think that I got bitten by that flaw. I was developing a little database
as a learning project and my primary keys were Autonumber data types.
Knowing that I was a newbie, and therefore prone to making errors with my
table designs, I was extremely cautious when entering my data. I would
enter 15 or 20 records, then check the database to make sure that everything
was going fine. Somewhere around the 75th record I started noticing
problems with the data from multiple tables getting mis-matched.
Fortunately, because I was constantly monitoring my data, I caught the
problem early--after only 4 or 5 records had gotten goofed up.

I had planned pretty carefully and done a lot of reading in the Access Help
before starting the project, and I couldn't see where I had gone wrong in
understanding the information that I had reviewed, so I posted a message to
a newsgroup (more than a year back). The responses indicated that
Autonumber should never be used for the primary key, but the arguments in
support of that position never really seemed to be convincing. Thanks for
setting the record straight.

-- Tom

MT DOJ Help Desk

Making the world a safer place.

 

[Allen Browne]

Great.

Can I suggest another "gotcha" to consider.

When you create relationships (Tools | Relationships), you probably check
the Relational Integrity box so that Access will not accept a record into
the related table if the foreign key does not match a record in the primary
table. That's great, but you can still end up with related records that have
Null in the foreign key.

To prevent that:
- Open the related table in design view.
- Select the foreign key field field.
- Set the Required property to Yes (lower pane of table design).