Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Location
    Sydney, NSW, Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dupplicate numbers in AutoID field (2000)

    Has anyone ever seen a situation where an AutoID field gives a duplicate number?

    Have set up a client server type database with forms, reports etc on local PC and tables on the file server. A user has entered a record and it has been assigned an auto ID, they have gone on to enter another couple of records then 15 minutes later, a new record has been assigned the same AutoID.

    The Primary key for that table includes another 2 fields. As these fields were different in each record no error was reported. It was only noticed when a relational field that uses the AutoID field was behaving strangely.

    Any ideas as to how this happened - (I haven't seen it in 7 years of developing databases in Access) I was able to 'fix' it by deleting and re-entering the data, but would like to stop it happening again.

    Thanks for any help

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dupplicate numbers in AutoID field (2000)

    I've never seen duplicate values in an AutoNumber field.

    You can set a unique index on the AutoNumber field. That should cause an error message if Access tried to assign a duplicate value to it.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Dupplicate numbers in AutoID field (2000)

    I've only seen this once, and it was in a corrupt database. It seems that somewhere I have also seen a list of fixes, I think for SR1 of A2k that indicated there was a bug that could cause this to happen. You might want to check and see if all of your users have at least SR1 installed.
    Wendell

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dupplicate numbers in AutoID field (2000)

    I've seen it once, in a corrupted database. However, I did have SR1 installed (we've never used A2K without SR1).

    A Compact and Repair got the autonumbers working correctly; however it rapidly became apparent in my case that there was other corruption in the database as well. So, be careful even if you think you have "fixed" the problem.

  5. #5
    New Lounger
    Join Date
    Apr 2002
    Location
    Sydney, NSW, Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dupplicate numbers in AutoID field (2000)

    I am in the process of updating to SR1a (had to find the original install CD ...)

    I think I will add the unique index to the AutoID field as a bit of a double check.

    The database is compacted nightly so hopefully it won't happen again...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •