Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AutoNumber generates non unique key (Access 2000)

    Trying to fix weird AutoNumber problem in a program written by long-gone programmer. Occasionally user cannot add records to a table with an AutoNumber key because the Add procedure creates an non-unqiue AutoNumber -- it is a lower number than the last AutoNumber in the table. The work-around has been to manually go to the table and type a few chars for new record (which generates incorrect Autonumber), hit ESCAPE, type a few chars for new record again (which increments the AutoNumber by 1), hit ESCAPE. Repeat until the Autonumber has been incremented to the correct value.

    Its installed on a network with several users.

    Is there a sleuth out there with ANY idea of where to start looking for the problem?? In the code that imports data into the table? Are there multi-user settings I should look at?

    Stumped, <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Gwemda

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

    Re: AutoNumber generates non unique key (Access 2000)

    You can over-ride autonumbers either thru importing or thru append queries. However, to my knowledge a <font color=red>non</font color=red>-corrupt database will always use the highest value plus 1 for any normal data entry of records with autonumber fields.

    I emphasized <font color=red>non</font color=red>-corrupt because I did have exactly what you described happen in an Access 97 database that I later realized was corrupt (other "weird" symptoms rapidly became apparent; I forget what they were) - for whatever reason the autonumbers in one of the tables "backed up" by 10 or so.

    As to fixing it, the quirk went away when I fixed the corrupted database, but like I said the database had had more problems than just funny autonumbers. Have you tried doing a compact and repair?

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

    Re: AutoNumber generates non unique key (Access 2000)

    Is this truly an Access Autonumber field, or is it one where the next record number is stored in a single row table? If it's the former, I suspect the database is corrupt as Doug suggests. If it's the latter, I would suspect a problem with the Add procedure. They could also have used the trick of doing a DMax on the ID number and then writing the record that way. That can be a problem if multiple people add records to the database. Let us know what you find out.
    Wendell

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: AutoNumber generates non unique key (Access 2000)

    In addition to corruption, this is caused by a bug in the vanilla release of Access 2000 where when you append values to the autonumber field, the autonumber algorithm does not consider those appended values when it calculates the next highest number. It was cured by SR-1.
    Charlotte

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoNumber generates non unique key (Access 2000)

    Thanks Douglas. I tried Compact & Repair ... I think Charlotte hit the nail on the head when she says its a problem fixed by SR-1. I didn't realize that you can over-ride autonumbers thru' importing or appending. Do you know of a good place to read up on this?
    Thanks again,
    Gwenda

  6. #6
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoNumber generates non unique key (Access 2000)

    Thanks Wendell. Yep -- its a real Autonumber field. I think Charlotte's suggestion to apply SR-1 is going to fix it. I'll let you know how it turns out.
    Gwenda

  7. #7
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoNumber generates non unique key (Access 2000)

    Charlotte, your brain ought to be enshrined. I'll get them to apply SR-1 and let you know what happens,.
    Thank you,
    Gwenda

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: AutoNumber generates non unique key (Access 2000)

    I can't take credit for any brilliance. I ran into the problem myself in my previous job and had to struggle with Microsoft Tech Support to get the answer. However, I can't assure you that SR-1 will do the trick. The MSKB does suggest, however, that a Jet service pack will if the Office SR-1 doesn't. See MSKB article: <A target="_blank" HREF=http://support.microsoft.com/default.aspx?scid=kb;en-us;Q257408&id=257408&SD=MSKB>ACC2000:AutoNumber Dupes Previous Values After Compact & Repair [Q257408]</A>
    Charlotte

Posting Permissions

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