Annoying Autonumber bug

[Ray Cacciatore]

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!

 

[Craig Hornish]

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/pfx/forum.aspx?webtag=ws-msdevapps

"Think outside the box, because anything is possible."
"How long it will take or whether it requires divine intervention is another
issue"

 

[Tom Wickerath]

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/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________