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 Fotolia.com)

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:
Easy

Other People Are Reading

Instructions

  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
    
        .Refresh
    
    End With
    

    End Sub

  3. 3

    Run the subroutine by pressing "F5."

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

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