Duplicate key on autonumber field! How did this happen?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 2 identical tables both with autonumber key, no other indexes.
I copy one table to the other via INSERT.. SELECT (not including ID column)
and I ger duplicate key. I noticed the autonumbers on the target table start
high (in datasheet view) then go low and then higher again. I believe the
autonumber being generated (only reason I can think of) already exists in the
table. How could this happen and how do I fix it? At some point I did do a
copy into this table from another database. I did compact and repair and it
did not help.
 
I have 2 identical tables both with autonumber key, no other indexes.
I copy one table to the other via INSERT.. SELECT (not including ID column)
and I ger duplicate key. I noticed the autonumbers on the target table start
high (in datasheet view) then go low and then higher again. I believe the
autonumber being generated (only reason I can think of) already exists in the
table. How could this happen and how do I fix it? At some point I did do a
copy into this table from another database. I did compact and repair and it
did not help.
Click to expand...

When you say you "belive the autonumber being generated already exists in the table", have you verified this? Do you
actually see duplicates in the Autonumber field?

What version of Access are you using? What version of Jet? There were some duplicate autonumber issues with older
versions of Jet, but I thought they had been fixed with one of the Service Packs. Jet 4.0 is currently at SP8, if I'm
not mistaken; update your Jet library and try it again.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com
 
I'm using Access 2003 (11.6566 SP2) under Windows XP Professional.
I dont know the Jet version.
No, I don't see duplicates in the autonumber but if the autonumber keeps
getting incremented it will hit the larger numbers at the top of the table.
The insert does hundreds of inserts so I cant tell which one generates the
duplicate.
The number of records to be added is less than the number needed to
increment the current autonumber to the point where it will coincide with the
higher numbers.
 
I could not wait for a solution since my users were screaming, so I solved
the problem by deleting the primary key from the target table.
 
I'm using Access 2003 (11.6566 SP2) under Windows XP Professional.
I dont know the Jet version.
No, I don't see duplicates in the autonumber but if the autonumber keeps
getting incremented it will hit the larger numbers at the top of the table.
The insert does hundreds of inserts so I cant tell which one generates the
duplicate.
Click to expand...

The Autonumber field is a Long Integer, so you've got somewhere around 5 billion possibilities ... I seriously doubt you
would ever "hit the limit", and if Access comes across a number already in use, it'll just move to the next number. If
you're really worried about it, set the Autonumber to increment Random ...
The number of records to be added is less than the number needed to
increment the current autonumber to the point where it will coincide with the
higher numbers.
Click to expand...

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com
 
Back
Top