Error 3022

[Guest]

I have compacted and repaired our tables. I have a table that is now getting
'duplicate index or key' . . . on some of the new records being created and
I see it is because the primary key counter in this table is at 800 (there
are records with primary keys in the table 800,802, 803, 804, 809 . . .).
So unless the primary counter key is 801, 805, 806, 807 and 808, we will get
errors. I thought when you had an autonumber as the key (incremented), that
if the number was out there it would automatically increment until there is a
valid number? HELP? How do I correct this? It seems that if you have
records deleted and then you do a compact/repair it resets the autocounter???
YIKES

Any advice is greatly appreciated!!!

THANKS!

Janis in Minnesota

 

[ruralguy via AccessMonster.com]

I believe MVP Allen Browne has covered this topic.
Fixing AutoNumbers when Access assigns negatives or duplicates
http://allenbrowne.com/ser-40.html

 

[Guest]

No, unfortunately this does not help. The key is not a negative number. The
table I have is 3 fields. The Primary Key (autonumber, increment), a
CustomerID and the Customer Name. There are over 2,000 records in this
table, a few have been deleted. Upon compacting and repairing, the
autonumber was reset to 790-ish. . . . the counter is at 800 and most of
these numbers exist as primary keys! HELP! I better re-read to see if I
missed anything . . .

Thanks for the advice and any more future!!

Janis

 

[ruralguy via AccessMonster.com]

The issue is for duplicate AutoNumbers as well and Allen has posted the
solution in that link. The trick is to reset the AutoNumber seed.

No, unfortunately this does not help. The key is not a negative number. The
table I have is 3 fields. The Primary Key (autonumber, increment), a
CustomerID and the Customer Name. There are over 2,000 records in this
table, a few have been deleted. Upon compacting and repairing, the
autonumber was reset to 790-ish. . . . the counter is at 800 and most of
these numbers exist as primary keys! HELP! I better re-read to see if I
missed anything . . .

Thanks for the advice and any more future!!

Janis

I believe MVP Allen Browne has covered this topic.
Fixing AutoNumbers when Access assigns negatives or duplicates

[quoted text clipped - 16 lines]