An identity field, or column, is used to create a unique way of accessing a row in a database table. Each identity value is unique and is automatically created when a new row is inserted. You can change an identity field by using a function in SQL Server and MySQL databases, and you need to reset the identity in Access by recreating the field.
Instructions
SQL Server
1. Open a SQL Server editor.
2. Add SQL code to reset the identity:
DBCC CHECKIDENT('Customer', RESEED, 0)
Replace the text "MyTable" with the name of the database table whose identity needs to be reset.
3. Run the code in SQL Server. The identity field will be reset to 0.
MySQL
4. Open a MySQL editor.
5. Add the following code:
USE pubs
GO
DBCC CHECKIDENT (MyTable, RESEED, 100)
GO
Replace the text "MyTable" with the name of the database table whose identity needs to be reset. Change the number "100" to whatever you would like the identity column to be set to.
6. Run the code in SQL editor. This will reset the value of the identity column to 100.
Microsoft Access
7. Open Access editor.
8. Add code to drop the identity column:
ALTER TABLE [table_name] DROP COLUMN [column_name]
Replace the text "table_name" and "column_name" with the values for your database table and identity column.
9. Add code to recreate the column:
ALTER TABLE [table_name] ADD COLUMN [column_name] IDENTITY (0,1)
Replace the text "table_name" and "column_name" with the values for your database table and identity column.
10. Run the code. The table identity field will be deleted and recreated. The identity field will be reset to 0.
Tags: database table, identity column, identity field, Replace text, column code, field will