AutoNumber generates non unique key (Access 2000)

Topic

New Reply

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,
Gwemda

Reply | Quote

Replies

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

I emphasized non-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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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: ACC2000:AutoNumber Dupes Previous Values After Compact & Repair [Q257408]

Reply | Quote