Yeah, I know what you’re thinking… “Why do autonumber fields need to be managed? Don’t they manage themselves?” Well, yes, autonumber fields are pretty much self-managing, but there are times when you, as a database manager, may need to repair a table with an autonumber field and you’re scared that you may mess it up, especially if the autonumber is used as a primary key and is referenced in other table foreign key fields.
For those of you who don’t know what an autonumber field is in an Access database table, it is a field that automatically generates and inserts a “long integer” number that is one number greater than the last number that it generated and inserted. For example, the first record added to the table would get an autonumber value of 1, the second record would get 2, and so on and so on. As long as you don’t have a situation where you have to replace missing records in the table with new records that have the original autonumber that the old records had, then you have nothing to worry about.
But if you do end up in such a situation, then you need to know how to handle it without having to kick everyone out of the database to do it. This is a common situation that database managers can find themselves facing after having to repair a corrupted database file. So, here is what you can do:
Did you know you could append records that have the number needed in the autonumber field if that number is not already in the table (no duplicate autonumbers allowed in the column)? The thing to remember is that if you append a record with a number in the autonumber field, that number becomes the new “seed” number that the field uses to calculate what the next number should be for the next record that is added to the table (without a number in the autonumber field). If you don’t adjust the seed number to one that won’t generate a duplicate, then it can create an error where you get a message that says you “can’t insert a record with a duplicate primary key or unique index”. The way you “fix” the issue is to insert a record with a number in the autonumber field that is one higher than the highest number already in the autonumber field. That will reset the seed number to one that will pick up the sequence where it left off and won’t create a duplicate number.
For example, if you have a table with 250 records and the autonumbers go from 1 to 250, then the next record appended to the table (without a value in the autonumber field) will get the value 251 in the autonumber field. However, let’s say you find (for some reason) that records 90 through 100 are missing and you need to replace them from a database backup file. You can simply append these records from the old table into the current one (with the autonumber included in the insert), and the records will go into the table with no problem. However, the seed number is now 100, and the next record added to the table will generate the autonumber value 101, which is already in the table, so an error is generated. To correct the situation, just append a record with 251 in the autonumber field. That will reset the seed number to 251, so the next one will be 252, and the series continues without creating a duplicate number.
Problem solved!!
