ACC: Programmatically Add Counter Field to Table Using DAO (113546)


The information in this article applies to:

  • Microsoft Access 2.0
  • Microsoft Access for Windows 95 7.0
  • Microsoft Access 97
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:
  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.

REFERENCES

For more information about the CreateField method, search for "CreateField," and then "CreateField Method" using the Microsoft Access 97 Help Index.
Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbinfo kbProgramming kbusage KB113546
©2004 Microsoft Corporation. All rights reserved.