ACC: Programmatically Add Counter Field to Table Using DAO

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

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

This article describes how to add an AutoNumber field (or Counter field in version 2.0) to an existing table using data access objects (DAO).

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 using DAO and Visual Basic, follow these steps:

1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).

2. Create the following new 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:

         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.

REFERENCES

For more information about the CreateField method, search for "CreateField," and then "CreateField Method" using the Microsoft Access 97 Help Index.

Keywords          : kbusage PgmHowTo MdlDao TblModfy
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbinfo