Reset Autonumber Seed

Reset autonumber seed

Please reference the following article, it contains a method you may add to your access project to execute to reset seeding. It has been a saver for me several occasions in the past:

http://allenbrowne.com/ser-40.html

In addition to this it gives explanation and insight into causes and potential resolution for such problems.

Reset AutoIncrement in SQL Server after Delete

Issue the following command to reseed mytable to start at 1:

DBCC CHECKIDENT (mytable, RESEED, 0)

Read about it in the Books on Line (BOL, SQL help). Also be careful that you don't have records higher than the seed you are setting.

How to reset AUTO_INCREMENT in MySQL

You can reset the counter with:

ALTER TABLE tablename AUTO_INCREMENT = 1

For InnoDB you cannot set the auto_increment value lower or equal to the highest current index. (quote from ViralPatel):

Note that you cannot reset the counter to a value less than or equal
to any that have already been used. For MyISAM, if the value is less
than or equal to the maximum value currently in the AUTO_INCREMENT
column, the value is reset to the current maximum plus one. For
InnoDB, if the value is less than the current maximum value in the
column, no error occurs and the current sequence value is not changed.

See How can I reset an MySQL AutoIncrement using a MAX value from another table? on how to dynamically get an acceptable value.

How to reset an Access table's AutoNumber field? (it didn't start from 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.

Access & SQL-Server: Resetting Autonumber field with table wipe

I went through all of my code, and checked with a local VBA expert who pointed out that I had made an error in the naming of my tables while attempting to do a pass-through query. I, basically, was referring to the table by the name it possess as a link in my front-end, and not the actual table name it possesses in SQL Server on the back end.

The following connection string, once this was corrected, worked:

strConnect = "ODBC;DRIVER={SQL Server};SERVER=ENV980-067\ENVIRON_TEST;DATABASE=instkeeper_test;TRUSTED_CONNECTION=YES;"

This, then, allowed me to execute the TRUNCATE TABLE command on the remote table, and execute a DoCmd.RunSQL statement to repopulate the table from the production source.

The end result is that when the option is selected to update the Activity, it will purge the contents of the remote table, then read the contents of the production table into the test table for use while resetting the autonumber.

Reset auto increment counter in postgres

If you created the table product with an id column, then the sequence is not simply called product, but rather product_id_seq (that is, ${table}_${column}_seq).

This is the ALTER SEQUENCE command you need:

ALTER SEQUENCE product_id_seq RESTART WITH 1453

You can see the sequences in your database using the \ds command in psql. If you do \d product and look at the default constraint for your column, the nextval(...) call will specify the sequence name too.

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

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.

Reorder / reset auto increment primary key

You could drop the primary key column and re-create it. All the ids should then be reassigned in order.

However this is probably a bad idea in most situations. If you have other tables that have foreign keys to this table then it will definitely not work.

Autonumber - specify starting point

You can use DDL under ADO to set the autonumber seed and increment. (However, echoing @mwolfe02's comment ... why?)

Dim strSql as String
strSql = "ALTER TABLE YourTable ALTER COLUMN YourAutoNumberColumn COUNTER(5000, 9)"
CurrentProject.Connection.Execute strSql

Also see if this previous Stack Overflow question is useful to you: How to reset autonumber seed - MS Access/VB6

Access AutoNumber sequence changed after Compact & Repair

An autonumber's seed value is not static. As soon as you add a row, the seed is reset to the previous seed value plus the increment.

An intentional feature with compact is to reset the seed based on the current values contained in the autonumber field.

There is no simple easy workaround.

You could create an autoexec macro to check your autonumber's current seed value and execute your DDL statement if it's less than 1001. That would set it as you wish each time you open the database.

Or you could add a dummy row to TestTable with ID = 1000, and make sure you never delete that row. But that's ugly.

Or you could make ID Long Integer and create your own custom auto-numbering code to determine the value you store there. But that's not easy or convenient.

---

Related Topics