Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

ACC2000: Cannot Change Default Seed and Increment Value in UI


View products that this article applies to.

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.

↑ Back to the top


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

↑ Back to the top


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.

↑ Back to the top


Article Info
Article ID : 202121
Revision : 3
Created on : 1/1/0001
Published on : 1/1/0001
Exists online : False
Views : 549