Primary key & Access auto num. faulty

[mackyrm]

I have built a database in Access 2003, it has been robust up until now. We noticed that some records were being added and some weren't through a form. I had a look at the ID numbers that Access was automatically assigning (primary keys) and realised that Access was sometimes assigning keys that were already used by existing records, i.e. the records that are not being added to the database are those that would appear to be using already assigned primary keys. I find it strange that Access wouldn't automatically assign a new ID from the greatest primary key ID number + 1.

Any advice on how to fix this would be appreciated. There may or may not have been a catalyst in this process...!

I recently imported a list of several hundred new records and did this by assigning primary keys externally (numbers that were not yet used by the database), it may just be a coincidence but the problem seems to have only recently occurred i.e. around the time of the import.

Regards and thanks

Macky

 

[Rabbie]

If your PK are defined correctly they should have the Indexed(No Duplicates) property set. This will stop you getting duplicate keys. Is it possible for you to post a copy of the table with the faulty keys and can you post the design view of this table.

 

[mackyrm]

Image Attached. All seems fine with the table index, any suggestions welcome!

If your PK are defined correctly they should have the Indexed(No Duplicates) property set. This will stop you getting duplicate keys. Is it possible for you to post a copy of the table with the faulty keys and can you post the design view of this table.

 

[mackyrm]

Tried entering data directly into table. Access is providing an already used autonumber, about 10 records prior to working last record. Can anyone advise?

 

[Rabbie]

I agree your index looks fine. I would try compacting and repairing your DB and if problem still continues try reinstalling Access. I would guess there is some corruption causing this. If everything else fails can you post your DB.

 

[neileg]

You could also try importing your objects into a new, blank database.

 

[gemma-the-husky]

there was a reported issue with access autonumbers being duplicated

its documented, and a solution is on the MS knowledge base - i would look there

sorry, i dont have the reference

i think you have to seed the autonumber, by adding a new record (append query) with an autonumber higher than the current - then you can delete it again, once its ok

its actually an issue with compact/repair so that WONT fix it, and its not a dbs installation problem

 

[odin1701]

I have this issue. Here is the code that I have to run in order to fix it.

The problem is that you have to run this code with exclusive access to the data table, so everyone has to be out of the database.

Compact/Repair will not fix it and nope not an install problem.

Here's the code:

Sub ResetAuto()

Dim iMaxID As Long
Dim sqlFixID As String

  iMaxID = DMax("ID", "tblAccounts") + 1
                       
  sqlFixID = "ALTER TABLE tblAccounts ALTER COLUMN ID COUNTER(" & iMaxID & ",1)"
  
  DoCmd.RunSQL sqlFixID

End Sub

ID is the AutoNumber field, and tblAccounts is whatever table is having this problem. If multiple tables, you'd have to do it once for each.

I run a compact/repair after that and it works fine.

The issue seems to crop up when using an autonumber in a database with a larger number of records in a multi-user environment. I don't remember the MSKB article that I found this in - I think I ripped the code from it.

I believe that this COULD change the autonumber assigned to a particular record, so the ID could change. As it's an autonumber, it shouldn't matter, but you should be aware that primary keys for records can be changed using this method, but there's no way around that.

Also, always backup before doing something like this, just in case.