Microsoft KB Archive/113546

= ACC: Programmatically Add Counter Field to Table Using DAO =

Article ID: 113546

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q113546





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

This article shows you how to use Data Access Objects (DAO) to add an AutoNumber field (or Counter field in version 2.0) to an existing table.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.



MORE INFORMATION
To add an AutoNumber field to a table with DAO and Visual Basic, follow these steps:  Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).  Create the following table called Counterless:      Table: Counterless ---     Field Name: MyText Data Type: Text Field Name: MyNumber Data Type: Number Field Name: MyDate Data Type: Date/Time  Save the table, 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 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 Database, TDef As TableDef, Fld As 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

' If you are using version 2.0, replace the ' two lines above with the following two lines. ' Set Fld = TDef.CreateField(FName, DB_LONG) ' Fld.Attributes = DB_AUTOINCRFIELD

' 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  Save the module as DAOAddCounter, and then close it.</li> To test this function, type the following line in the Debug 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">