How to restart counting from 1 after erasing table in MS Access?

Solution 1:

In Access 2010 or newer, go to Database Tools and click Compact and Repair Database, and it will automatically reset the ID.

Solution 2:

You can use:

CurrentDb.Execute "ALTER TABLE yourTable ALTER COLUMN myID COUNTER(1,1)"

I hope you have no relationships that use this table, I hope it is empty, and I hope you understand that all you can (mostly) rely on an autonumber to be is unique. You can get gaps, jumps, very large or even negative numbers, depending on the circumstances. If your autonumber means something, you have a major problem waiting to happen.

Solution 3:

In addition to all the concerns expressed about why you give a rat's ass what the ID value is (all are correct that you shouldn't), let me add this to the mix:

If you've deleted all the records from the table, compacting the database will reset the seed value back to its original value.

For a table where there are still records, and you've inserted a value into the Autonumber field that is lower than the highest value, you have to use @Remou's method to reset the seed value. This also applies if you want to reset to the Max+1 in a table where records have been deleted, e.g., 300 records, last ID of 300, delete 201-300, compact won't reset the counter (you have to use @Remou's method -- this was not the case in earlier versions of Jet, and, indeed, in early versions of Jet 4, the first Jet version that allowed manipulating the seed value programatically).

Solution 4:

I am going to Necro this topic.

Starting around ms-access-2016, you can execute Data Definition Queries (DDQ) through Macro's

Data Definition Query

ALTER TABLE <Table> ALTER COLUMN <ID_Field> COUNTER(1,1);

1. Save the DDQ, with your values
2. Create a Macro with the appropriate logic either before this or after.
3. To execute this DDQ:
   • Add an Open Query action
   • Define the name of the DDQ in the Query Name field; View and Data Mode settings are not relevant and can leave the default values

WARNINGS!!!!

1. This will reset the AutoNumber Counter to 1
2. Any Referential Integrity will be summarily destroyed

Advice

• Use this for Staging tables
  • these are tables that are never intended to persist the data they temporarily contain.
  • The data contained is only there until additional cleaning actions have been performed and stored in the appropriate table(s).
  • Once cleaning operations have been performed and the data is no longer needed, these tables are summarily purged of any data contained.
• Import Tables
  • These are very similar to Staging Tables but tend to only have two columns: ID and RowValue
  • Since these are typically used to import RAW data from a general file format (TXT, RTF, CSV, XML, etc.), the data contained does not persist past the processing lifecycle