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:
Other People Are Reading
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."
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
Run the subroutine by pressing "F5."
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for