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

Question

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 1

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.

Answer 2

Looks like your only option is to move the data into a new table. The following link has some information about how to do it based on your version of access.

Note: be careful if you have relationships to other tables as those would need to be recreated.

http://support.microsoft.com/kb/812718

Answer 3

I ran across this little tid bit of info on how to set the value of a Microsoft Access AutoNumber Field.

Setting the value of a Microsoft Access AutoNumber Field

Using an Append Query to Set the Initial Value of a Microsoft Access AutoNumber Field:

By using an append query, you can change the starting value of an AutoNumber field in a table
to a number other than 1.

Microsoft Access always numbers AutoNumber fields beginning with the number 1. 
If the table has  data in it already, the starting value of the autonumber will be higher 
than the highest value already in the table. You cannot manually edit an AutoNumber 
field or change its starting value.

Overview: Changing Initial Value of an AutoNumber Field

In order to force Microsoft Access to number an AutoNumber field with a number you choose,   
follow these general steps below:

For a new table that contains no records, you can change the starting value of an AutoNumber
field that has its NewValues property set to Increment to a number other than 1. For a table 
that contains records, you can also use this procedure to change the next value assigned in an
AutoNumber field to a new number.

    1. Create a temporary table with just one field, a Number field; set its FieldSize 
       property to Long Integer and give it the same name as the AutoNumber field in the table
       whose value you want to change.

    2. In Datasheet view, enter a value in the Number field of the temporary table that is 1 
       less than the starting value you want for the AutoNumber field. For example, if you want
       the AutoNumber field to start at 100, enter 99 in the Number field.

    3. Create and run an append query to append the temporary table to the table whose 
       AutoNumber value you want to change. 

Note: If your original table has a primary key, you must temporarily remove the primary key 
before running the append query. Also, if your original table contains fields that have the
Required property set to Yes, the Indexed property set to Yes (No Duplicates), or field and/or 
record ValidationRule property settings that prevent Null entries in fields, you must 
temporarily disable these settings.

     4. Delete the temporary table.
     5. Delete the record added by the append query. 
     6. If you had to disable property settings in step 3, return them to their original 
        settings.

When you enter a record in the remaining table, Microsoft Access uses an AutoNumber field 
value 1 greater than the value you entered in the temporary table. 

Note: If you want to compact the database after changing the starting AutoNumber value, make
sure to add at least one record to the table first. If you don't, when you compact the database,
the AutoNumber value for the next record added will be reset to 1 more than the highest previous
value. For example, if there were no records in the table when you reset the starting value,
compacting would set the AutoNumber value for the next record added to 1; if there were records
in the table when you reset the starting value and the highest previous value was 50, compacting 
would set the AutoNumber value for the next record added to 51.

It worked for me find. Just follow the instructions to the letter. Not like me skipping around though it. I found out the hard way to do exactly as it says. I hope it'll help you out if I read your question right. I restart the autonumber field to 4556363 with a table with 8500 records in it and it didn't alter anything, just the autonumber field. I hope this ain't to late to help. Steven