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
|
|||
|
|||
Annoying Autonumber bug
There's a bug in Access where the Autonumber reuses old numbers as the
primary key. According to Microsoft, this bug was supposed to be fixed in the latest Jet version...Not! Apparently, every time you compact your database, some tables may reuse old numbers as the value in your Autonumber field. Now, my users get "Duplicate values" error when they add records in a table. Help! |
#2
|
|||
|
|||
Annoying Autonumber bug
Well in my version it works - but it does reset if you delete all the
records - I'll let others confirm versions and service pacts you need. But my thought on this is that the Error you gave is not possible given the limited information you provided. Assuming autonumber field is Primary as you say - it can't duplicate numbers. If an old number is reused then worst case is it will return false data in other tables where it is used as a foriegn key. And you have a bigger problem with orphaned records. I could be missing something and if anybody could provide the situation where this is possible as Ray has discribed I would be interested. -- Craig Hornish Visit weekly Access conferences Tuesday, 11:00am-12:30pm (Eastern US) Thursday, 3:30pm- 5:00pm (Eastern US) http://community.compuserve.com/n/pf...g=ws-msdevapps "Think outside the box, because anything is possible." "How long it will take or whether it requires divine intervention is another issue" "Ray Cacciatore" wrote in message ... There's a bug in Access where the Autonumber reuses old numbers as the primary key. According to Microsoft, this bug was supposed to be fixed in the latest Jet version...Not! Apparently, every time you compact your database, some tables may reuse old numbers as the value in your Autonumber field. Now, my users get "Duplicate values" error when they add records in a table. Help! |
#3
|
|||
|
|||
Annoying Autonumber bug
Hi Ray,
I have seen this problem, even though I have the latest service pack (SP-8) for the JET database engine. The database in question was created with an older version of the JET, which was suseptible to this problem. In order to fix the problem, I had to delete the autonumber field, compact the database, and then create a new field. If you have related tables that are child to this table, then you will need to create a new empty table (you can copy the structure only of your existing table), and then run an append query to append the data from the affected table to your new table. You can append to an autonumber field, and it should accept the value, as long as it has not already been used. Tom Wickerath Microsoft Access MVP http://www.access.qbuilt.com/html/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "Ray Cacciatore" wrote: There's a bug in Access where the Autonumber reuses old numbers as the primary key. According to Microsoft, this bug was supposed to be fixed in the latest Jet version...Not! Apparently, every time you compact your database, some tables may reuse old numbers as the value in your Autonumber field. Now, my users get "Duplicate values" error when they add records in a table. Help! |
Thread Tools | |
Display Modes | |
|
|