How to insert old records into an Auto Numbered table
This is an old problem that rears it’s ugly head from time-to-time. You have a transaction table which uses an automatically generated number (Auto Number) as the record ID. You have lost some records in your database due to a corruption, and you have an old backup of the database which contains the lost records. You need to insert these old records back into your current database, but you need to keep the original ID because it has dependancies.
The problem is that your table has an Auto Number ID, and if you insert the “lost” records, you will be given the next available number as the ID for your old records.
Before you attempt any of the instructions below, take a backup of your database!
Here is what I have done:-
- Delete all relationships to your destination table (“tblYourTable”).
- Create a copy of your table “tblYourTable” to “tblYourTableNew”, without copying the data, ie., “structure only”.
- Change the Data Type for field “YourRecordID” from AutoNumber to Number (Long Integer) on the original table “tblYourTable”, and save the changes.
- Insert the lost record(s) into table “tblYourTable” from the backup database, and note that the value of field “YourRecordID” has not changed for the reinstated records.
- All being well, append all records from table “tblYourTable” to the new table, “tblYourTableNew”.
- Check that all records have been loaded into the new table, and that the numbers in field “YourRecordID” match. You can write a query to do this.
- Rename table “tblYourTable” to something else, say “tblYourTableOld”.
- Now, rename table “tblYourTableNew” to “tblYourTable”, and recreate the table relationships you deleted in step 1.
- If everything is as it should be, table “tblYourTableOld” can now be deleted.
The lesson to be learned from this exercise, is to give fair consideration as to how and when it is appropriate to use an auto number key on a table during the design phase of your database. Bad table design can always be rectified later, but sometimes the effort is considerable, and costly.
Top 10 database design tips Using VBA Internal Subroutines