In previous articles, we have shown examples of How to Reset the Microsoft Access AutoNumber in Microsoft Access 97, 2000 and Microsoft Access XP (2002). We have also dealt with Using an Append Query to Set the Initial Value of an AutoNumber Field.
In the latest version of Microsoft Access, Microsoft Access 2003, resetting the AutoNumber may not work using the previous methods as Microsoft have discovered a bug in the process as detailed in:
BUG: AutoNumber field is not reset after you compact an Access database
The symptoms mentioned in the article are detailed as:
"When you delete the last record or records in a table or in a form that has an AutoNumber field, and then you compact the database, the AutoNumber field does not start at the next higher number when you add new records."
The cause of the bug appears to be shown as: "You have installed Microsoft Jet 4.0 Service Pack 4 (Msjet40.dll version 4.00.2927.4) or later."
The article then goes on to detail the work around's, and gives methods as to how to rectify this issue.
If you are looking to reset the Microsoft Access AutoNumber is previous versions of Access then the following articles are still valid: