How to Access a Make Table Query Autonumber

Written by jaime avelar
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Access a Make Table Query Autonumber
Learn how to create an auto number field in Access using VBA. (digital city. binary tunnel and city at night image by Stasys Eidiejus from

Knowing how to programmatically create a table and then add an auto number field using Visual Basic for Applications (VBA) can save you time when working with Microsoft Office Access. Access is a relational database management system included in the Microsoft Office suite. VBA is a computer programming language used in Microsoft Office applications to automate manual processes such as creating tables. In a few steps you can write VBA code to create a table, add data to it and add an auto number field to it.

Skill level:


  1. 1

    Open Microsoft Office Access 2007 and click "Blank Database," then select "Create." Click "Database Tools," then select "Visual Basic" to open the Microsoft Visual Basic Window. Select the "Insert" field and click "Module."

  2. 2

    Copy and paste the following code into the module:

    Private Sub createAutoNumberField()

    Set dbs = Application.CurrentDb
    sQLString = "CREATE TABLE InstrumentInfo (Instrument TEXT, SerialNumber TEXT)"
    DoCmd.SetWarnings False
    DoCmd.RunSQL (sQLString)
    strsql = "INSERT INTO InstrumentInfo VALUES('MXA','83456')"
    DoCmd.RunSQL (strsql)
    strsql = "INSERT INTO InstrumentInfo VALUES('Signal Generator','1244532')"
    DoCmd.RunSQL (strsql)
    Set tblDef = dbs.TableDefs("InstrumentInfo")
    Set auto Field = tblDef.CreateField("AutoColumn", dbLong)
    With auto Field
        .Attributes = dbAutoIncrField
    End With
    With tblDef.Fields
        .Append auto Field
    End With

    End Sub

  3. 3

    Run the subroutine by pressing "F5."

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.