ACC2000: Can't Change Default Seed and Increment Value in UI

• Microsoft Access 2000

SYMPTOMS

In Microsoft Access 2000, you can change the seed and 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

Use Data Definition Language (DDL) to create a table with an Identity column that has the seed and 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 demonstrate how to do this:

1. Open the sample database Northwind.mdb.



2. On the Tools menu, click References. In the list of available references, click to select (check) Microsoft ActiveX Data Objects 2.1 Library.



3. Create a module and type the following line in the Declarations section if it is not already there:




Option Explicit 

4. Type the following procedure:




'This Example creates a table 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 

5. In the Immediate window, type the following line and then press ENTER:

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

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



6. Open tblEmployees in Design view and add any other columns that you need to the new table.
   
   NOTE: You need to add at least one column to complete the next step.



7. Switch to Datasheet view and add a few new records. Note the EmpID for the first record is 1000, the second 1005, and so forth.

MORE INFORMATION

Steps to Reproduce Behavior

1. Create a new Microsoft Access database.



2. Create the following table named 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 increment values in Design view.



3. Save the table as Table1. When 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 subsequent records is incremented by one.

Additional query words: prb ActiveX Data Objects ADO

Keywords          : kbdta 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbprb 

Last Reviewed: May 13, 1999