How To Reset The AutoNumber Field In MS-Access

AFAIK you can't, without deleting the field and re-creating it.

There are good reasons for this.
1) Autonumber is often used (unfortunately) as an index to the table. This means there could exist other rows which refer to a now deleted row by the previous autonumber. If you could reset the number, then those rows would eventually be associated with a new row instead.

In your case, imagine what trouble it would cause if you used the autonumber as an invoice number, and had a separate tablw of items on the invoice. these would then refer back to the invoice by the autonumber. If you reset the number, suddenly a customer gets invoiced for thirty items he didn't order, and hasn't had. Trouble ensues.

Personally, I hate autonumber and only ever use it when I will never refer to it: as an index for a log table for example. If you need a specific sequence of invoice numbers (and legally you do in most areas) consider writing a stored procedure to allocate a new invoice number and use a Guid for the actual database row reference instead.

You can execute an Access DDL statement from ADO to reset the autonumber seed value. Here is an example Immediate window session:

strDdl = "ALTER TABLE Dummy ALTER COLUMN ID COUNTER(1, 1);"
CurrentProject.Connection.Execute strDdl

The statement must be executed from ADO. It will fail if you try it with DAO (such as CurrentDb.Execute strDdl), or from the Access query designer. The example succeeded because CurrentProject.Connection is an ADO object.

The two values following COUNTER are seed and increment. So if I wanted the autonumber to start from 1000 and increment by 2, I could use COUNTER(1000, 2)

If the table contains data, the seed value must be greater than the maximum stored value. If the table is empty when you execute the statement, that will not be an issue.

------------
http://stackoverflow.com/questions/20738596/how-to-reset-an-access-tables-autonumber-field-it-didnt-start-from-1[^]

Credit goes to -> HansUp

ALTER TABLE Table1 ALTER COLUMN ID COUNTER(0,1)

Easy way is to..

delete any blank records in table

rename to table0 - if warned about relationships, write them down and delete them

copy the table structure by right-clicking on the table and picking structure only - save that as table

right-click table0, pick move data to another table - pick table to send it to

problem solved
make sure your data made it to new table then it's OK to delete table0
reset your relationships

compact the database and then try the insert again