Thread: Autonumber is out of sequence
11-30-12, 09:19 #1Registered User
- Join Date
- Aug 2010
Unanswered: Autonumber is out of sequence
I have a table with the Autonumber function to create an id number for each record in the table. It is the key for the table. The only purpose of this number is to ensure a unique ID for each record in this table.
However, the Autonumber function is automatically generating values that are about 7 values less than the previously saved value in the table, therefore, this value is not unique, and I get a key violation.
I thought I tricked the table by generating and deleting 6 new records, until the autonumber generated a value of the last saved record, and then I closed the table and my append query started functioning again, to add new records to this table.
But after closing the database, and reopening, the autonumber problem comes back.
I have the database set to "compact on close", and there are about 7000 records in the table. Some records have been deleted, so the autonumber field does have some gaps in the numbers from 1 to 7000.
How do I keep the autonumber field in line with the last number it generated? I figured that the database would keep track of that on it's own, and it has for a long time (I've been using this database for about 2 year).
I am using Access 2010.
11-30-12, 10:14 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
gaps don't matter, its in internal reference number an autonumber column shoudl have no meaning or value beyond that or outside the system
my guess is the compact on close is possibly the source of the problem. if you've lasted for 2 years with compact on close then you have done well.
as to resolutions
take a backup
..but beyond that I don't know
i'd try changing the datatype from autonumber to long
save the table, compact and repair the app.
then set the column back to autonumber.. see if that clears it.
in any event drop the idea of compact on close...I'd rather be riding on the Tiger 800 or the Norton
11-30-12, 16:45 #3Moderator
Provided Answers: 19
- Join Date
- Jun 2005
- Richmond, Virginia USA
Here's a link to Allen Browne's article on the problem:
Microsoft Access tips: Fixing AutoNumbers when Access assigns negatives or duplicates
Linq ;0)>Hope this helps!
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007