Microsoft KB Archive/202121

= 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:

 Open the sample database Northwind.mdb. On the Tools menu, click References. In the list of available References, click to select Microsoft ActiveX Data Objects 2.1 Library.  Create a module, and then 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 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   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. </li> 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.</li> 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.</li></ol>

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

<div class="moreinformation_section">

Steps to Reproduce the Behavior
<ol> Create a new Microsoft Access database.</li>  Create the following table and name the table Table1 : <pre class="fixed_text">  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.

</li> Save the table as Table1. When you are prompted to create a primary key, click No.</li> On the View menu, click Datasheet View.</li> 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.</li></ol>

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

Keywords: kbprb KB202121

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.