ACC: Programmatically Add Counter Field to Table Using DAO

MORE INFORMATION

1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).
2. 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

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

   Option Explicit

5. 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
   						

6. Save the module as DAOAddCounter, and then close it.
7. 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.