How to reset an Access table's AutoNumber field? (it didn't start from 1)

sql ms-access sql-insert autonumber
Lucas Juan picture Lucas Juan · Dec 23, 2013 · Viewed 21.1k times · Source

I have a INSERT INTO ... SELECT statement that copies data from one table to another.

The thing though is, the AutoNumber column value in the second table started from the last number in the first one.
Meaning the count of first table is 2000, then, the second table started from 2001.

Using an Access database, how to reset this value?

Answer

HansUp picture HansUp · Jan 15, 2014

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.

Related questions

SQL INSERT INTO statement for MS Access

I am trying to learn how to use MS Access with my VB.net program. I am practicing learning how to use the INSERT INTO statement but I keep getting an error. Here is my code: Imports System.Data.OleDb …

Read More
How to do INSERT into a table records extracted from another table

I'm trying to write a query that extracts and transforms data from a table and then insert those data into another table. Yes, this is a data warehousing query and I'm doing it in MS Access. So basically I want …

Read More
How to SUM two fields within an SQL query

I need to get the total of two fields which are within the same row and input that number in a field at the end of that same row. This is my code. Sum(tbl1.fld1 + tbl1.fld2) AS [Total] …

Read More