ACC2000: How to Use DAO to Programmatically Add an AutoNumber Field to a Table (210405)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

To add an AutoNumber field to a table using DAO and Visual Basic code, follow these steps:

1. Start Microsoft Access and open any database.
2. Create the following table:

   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 as Counterless, 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 or paste 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 DAO.Database, TDef As DAO.TableDef, Fld As DAO.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
   
      ' 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. To test this function, type the following line in the Immediate window, and then press ENTER:

   ?AddCounter("Counterless","NewID")

   Open the Counterless table and note the new "NewID" AutoNumber field.