Microsoft KB Archive/210405

= ACC2000: How to Use DAO to Programmatically Add an AutoNumber Field to a Table =

Article ID: 210405

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210405



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

This article applies only to a Microsoft Access database (.mdb).



SUMMARY
This article shows you how to use Data Access Objects (DAO) to add an AutoNumber field to an existing table.



MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

To add an AutoNumber field to a table using DAO and Visual Basic code, follow these steps:  Start Microsoft Access and open any database. Create the following table:

Table: Counterless

Field Name: MyText

Data Type: Text

Field Name: MyNumber

Data Type: Number

Field Name: MyDate

Data Type: Date/Time

 Save the table as Counterless, close it, and do not create a primary key. Create a module and type the following line in the Declarations section if it is not already there:

Option Explicit

  Type or paste the following procedure: '*************************************************************** ' FUNCTION: AddCounter ' ' PURPOSE: Programmatically adds an AutoNumber field to an '           existing table. ' ' ARGUMENTS: ' '   TName: The name of the table. '   FName: The name of the new AutoNumber field. ' ' RETURNS: True (error was encountered) or '           False (no error) as an integer. ' '***************************************************************

Function AddCounter (TName As String, FName As String) As Integer Dim DB As DAO.Database, TDef As DAO.TableDef, Fld As DAO.Field

' Get the current database. Set DB = CurrentDb ' Open the tabledef to which the counter field will be added. Set TDef = DB.TableDefs(TName)

' Create a new AutoNumber field of type LONG ' with the automatic increment attribute. Set Fld = TDef.CreateField(FName, dbLong) Fld.Attributes = dbAutoIncrField

' Trap for any errors. On Error Resume Next

' Append the new field to the tabledef. TDef.fields.Append Fld

' Check to see if an error occurred. If Err Then AddCounter = False Else AddCounter = True End If

DB.Close

End Function  To test this function, type the following line in the Immediate window, and then press ENTER:

?AddCounter("Counterless","NewID")

Open the Counterless table and note the new "NewID" AutoNumber field.</li></ol>

<div class="references_section">