ACC: Programmatically Add AutoNumber Field to Table with DAO

ID: Q113546

The information in this article applies to:

• Microsoft Access versions 2.0, 7.0, 97

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:

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

4. Create a module and type the following line in the Declarations section

   if it is not already there:

      Option Explicit

      '***************************************************************
      ' 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

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.

Additional query words:

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

Last Reviewed: November 21, 1998