Menu
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?
386
For example, if you want the AutoNumber field to start at 100, enter 99 in the Number field. Create and run an append query to append the temporary table to the table whose AutoNumber value you want to change.
For new tables, AutoNumber begins with 1, and is incremented by 1 with each new row. However, we occasionally want to start the AutoNumber at a number higher than 1. To do this, create an Append query to insert the value of the ID one less than your desired starting number (or Seed value).
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.
83
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
43
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
