If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
AutoNumber for Primary Key
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. |
#2
|
|||
|
|||
AutoNumber for Primary Key
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "MT DOJ Help Desk" wrote in message ... 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. |
#3
|
|||
|
|||
AutoNumber for Primary Key
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. |
#4
|
|||
|
|||
AutoNumber for Primary Key
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" wrote in message . au... 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. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "MT DOJ Help Desk" wrote in message ... 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. |
#5
|
|||
|
|||
AutoNumber for Primary Key
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). -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "MT DOJ Help Desk" wrote in message ... 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" wrote in message . au... 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. "MT DOJ Help Desk" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|