Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: How to Use DAO to Programmatically Add an AutoNumber Field to a Table

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

View products that this article applies to.

This article was previously published under Q210405
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

↑ Back to the top

Summary

This article shows you how to use Data Access Objects (DAO) to add an AutoNumber field to an existing table.

↑ Back to the top

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.

↑ Back to the top

References

For more information about the CreateField method, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type createfield method in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

↑ Back to the top

Keywords: KB210405, kbusage, kbhowto

↑ Back to the top

Article Info
Article ID : 210405
Revision : 5
Created on : 10/11/2006
Published on : 10/11/2006
Exists online : False
Views : 338