Microsoft KB Archive/202121

Knowledge Base

ACC2000: Cannot Change Default Seed and Increment Value in UI

Article ID: 202121

Article Last Modified on 6/28/2004

---

APPLIES TO

• Microsoft Access 2000 Standard Edition

---

This article was previously published under Q202121

Advanced: Requires expert coding, interoperability, and multiuser skills.

SYMPTOMS

In Microsoft Access 2000, you can change the seed and the increment value of an AutoNumber field, otherwise known as an Identity column, from the default value of one. However, there are no options available in the user interface (UI) for you to make this change.

RESOLUTION

To resolve this problem, you can use Data Definition Language (DDL) to create a table with an Identity column that has the seed and the increment values that you want, other than the default value of one. Then, add the remaining fields that you want in Design view.

The following steps show you how to do this:

1. Open the sample database Northwind.mdb.
2. On the Tools menu, click References.
3. In the list of available References, click to select Microsoft ActiveX Data Objects 2.1 Library.

4. Create a module, and then type the following line in the "Declarations" section if it is not already there:

   Option Explicit
                       

5. Type the following procedure:

   'This Example creates a table that is called tblEmployees with
   'custom identity seed values.
   
   Function CreateNewTable(tName As String, colName As String, _
                            vSeed As Integer, vInc As Integer)
   
       Dim conn As ADODB.Connection
       Set conn = CurrentProject.Connection
       conn.Execute "Create Table " & tName & "(" & colName & _
                    " Identity(" & vSeed & ", " & vInc & "));"
   
       Application.RefreshDatabaseWindow
   
   End Function
                       

6. Type the following line in the Immediate window, and then press ENTER:

   ?CreateNewTable("tblEmployees", "EmpID", 1000, 5)
                           

   Note that a new table that is named tblEmployees appears in the Database window. The table, tblEmployees, has one column called EmpID that is an IDENTITY property with a seed of 1000 and an increment of five.

7. Open tblEmployees in Design view, and then add any other columns to the new table that you have to.
   
   NOTE: You must add at least one column to complete the next step.
8. Switch to Datasheet view, and then add a few new records. Note the EmpID for the first record is 1000, the second is 1005, and so on.

NOTE: When you copy, export, import, transfer tables into a new table or database, or delete all records in the table and compact the database, the IDENTITY property seed or the increment values, or both, are set back to the default of 1. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

202117 ACC2000: Jet IDENTITY Datatype Seed and Increment Reset to 1

MORE INFORMATION

Steps to Reproduce the Behavior

1. Create a new Microsoft Access database.

2. Create the following table and name the table Table1:

      Table: Table1
      -------------------------------
      Field Name: CategoryID
         Data Type: AutoNumber
         Indexed: Yes (No Duplicates)
      Field Name: Category Name
         Data Type: Text
                       

   Note that no options are available to set the seed or the increment values in Design view.
   
   

3. Save the table as Table1. When you are prompted to create a primary key, click No.
4. On the View menu, click Datasheet View.
5. Enter a few records.
   
   Note that the first record has a CategoryID of one, and that the CategoryID of records after that is incremented by one.

Additional query words: prb ActiveX Data Objects ADO can t cannot

Keywords: kbprb KB202121

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.