ACC2000: Jet IDENTITY Datatype Seed and Increment Reset to 1

View products that this article applies to.

This article was previously published under Q202117

Moderate: Requires basic macro, coding, and interoperability skills.

SYMPTOMS

When you copy, export, import, or transfer tables into a new table or database, the IDENTITY property seed and increment values are both set to the default of 1.

Back to the top

CAUSE

You will experience this behavior when you set the seed or increment values of the IDENTITY property to a value other than one, and then create a new table using one of the following methods:

On the File menu, click Save As.
On the File menu, click Export.
On the File menu, point to Get External Data, and then click Import.
On the Edit menu, click Copy. Then on the Edit menu, click Paste.
Use the Visual Basic for Applications TransferDatabase method.

Back to the top

There is no option available in the user interface to change the seed and increment values of an existing column. However, you can use one of two methods that use the Data Definition Language (DDL) to change the seed and increment values for a newly copied or imported table.

Back to the top

METHOD 1

Create a new table with an IDENTITY column that has the seed and increment values that you want, other than the default value of 1. Then add the remaining fields in Design view.

The following steps demonstrate how to do this:

Open the sample database Northwind.mdb.
Create a module and type the following line in the Declarations section if it is not already there:
Option Explicit

Type the following procedure:

'This Example creates a table with custom identity seed values.

Function CreateNewTable(tName As String, colName As String, _
      vSeed As Integer, vInc As Integer)

    DoCmd.RunSQL "Create Table " & tName & _
    "(CustID Identity(" & vSeed & "," & vInc & "));"

    Application.RefreshDatabaseWindow
End Function

In the Immediate window, type in the following:
?CreateTable("New_tblEmployees","EmpID",1000,5)
Note that a new table called New_tblEmployees appears in the tables list in the Database window. The tblEmployees table has one column called EmpID, which is an IDENTITY property with a seed of 1000 and an increment of 5.
In Design view of the table, you can add any other columns that you need.
In Datasheet view, try adding a few new records. Note that the first record is numbered 1000, the second 1005, and so on.

At this point, if needed, you can use an append query to move the data, except for EmpID, from the original table into the new one.

Back to the top

METHOD 2

Use the ALTER TABLE command to change the seed and increment values in the column that has been reset.

NOTE: Before using ALTER TABLE, always make a backup copy of the table.

NOTE: Make sure you are correctly setting the seed value to the next one in the sequence. Setting a seed value too high results in skipped numbering. First, in Datasheet view, sort the table in ascending order by the column with the IDENTITY property to determine the value of the last record. For instance, if your data increments by 5 and the last entry is 2005, then you should set the seed value to 2010 and the increment to 5.

To run the ALTER TABLE command, follow these steps. This example changes a table to have a seed value of 10 and an increment of 4:

Open the sample database Northwind.mdb.
Select the Categories table and on the Edit menu, click Copy.
In the Paste Copy As dialog box, name the new table tblCat.
Create a new query in Design view.
Click Close in the Show Table box.
On the Query menu, point to SQL Specific, and then click Data Definition.
In the Data Definition Query window, type the following SQL statement:
ALTER TABLE tblCat ALTER COLUMN CategoryID COUNTER (10,4);
Save and close the query as qryAlterTable.
On the Query tab, double-click qryAlterTable to run it.
Open the table, tblCat, in Datasheet view, and enter a couple of new records.

Note that the first new record is numbered 10 and the next is 14.

Back to the top

MORE INFORMATION

Steps to Reproduce Behavior

Create a new Microsoft Access database.
On the Insert menu, click Module.
On the Tools menu, click References.
Click to select (check) the Microsoft ActiveX Data Objects 2.1 Library check box and the Microsoft ADO Ext. 2.1 for DDL and Security check box. Click OK.

Type the following procedure:

Sub Set_IDENTITY_Properties()

   'Connects to native Jet 4.0 OLE-DB Provider (MSJETOR40.dll)
   Dim strConn As String
   Dim adoConn As ADODB.Connection
   Dim adoCmd As ADODB.Command
   Dim adoTbl As New ADOX.Table

   'Use the ADO connection to the database that's already in place
   Set adoConn = CurrentProject.Connection
   Set adoCmd = New ADODB.Command

   'Use a Command Object to issue an SQL statement
   With adoCmd
      .ActiveConnection = adoConn
      .CommandType = adCmdText
      .CommandText = "CREATE TABLE New_tblEmployees(EmpID " _
         & "IDENTITY(10,5),EmpName CHAR)"
      .Execute

      'Insert records into the table just created.
      .CommandText = "INSERT INTO New_tblEmployees (EmpName) " _
         & "SELECT 'Kevin' AS Expr1;"
      .Execute
      .CommandText = "INSERT INTO New_tblEmployees (EmpName) " _
         & "SELECT 'Russ' AS Expr1;"
      .Execute
   End With

End Sub

On the Run menu, click Run Sub/UserForm.
Open the New_tblEmployees table and add two new records. Note that the EmpID increments by five.
On the File menu, click Close.
Click the New_tblEmployees table. On the File menu, click Save as. Keep the default values and click OK.
Open the Copy of New_tblEmployees table, and then add two new records. Note that the EmpID now increments by one.

NOTE: If you copy just the structure, the seed value is also reset to one