Microsoft Access – Add missing record for Auto-Number records

One of my customers use an access “application” where surprisingly one record was missing for an auto-number field. Therefore one invoice number was missing which can lead to many questions when the finance is asking where the number is.

Here are the steps how to add a the missing record.
Note: Its a little bit complicated for such a simple task but i found no other solution.

1. Backup your database
2. Open the access database
3. Open the broken table
4. Change the field from “AutoNumber” to “Number”
5. Switch to View mode and add the missing record, here it was “6914”
   
6. Check if you have now the right number of records according to the number of the last record, here it was the invoice number 6942
   
7. Copy the Table

   
8. Paste only the structure of the table

   
   
9. Change the field to “AutoNumber” and save it

   
10. Create a query with query wizard as shown below

    
    
    
11. Switch to design mode an append to the structure only table

    
    
    
    
12. Close and open the structure table
13. Rename delete the original table and rename the structure table to the original name
14. Create a new record, now you should get a new ID without missing records