Back To Top

Create Autonumber Field with VBA For MS Access 2000, 2002, 2003, 2007

Actual VBA Code and Sample at the bottom of the page. Follow the Autonumber Link to create one in Design View (manually).

Go here To Check if Table exists in VBA
Go here To Check if Field exists in VBA
Go here To Update a Field in VBA
Go Here to Change a field’s data type in VBA

This example came about from a need to create an Autonumber field in a MS Access Database. The record count was somewhere around 200k-300k. I went to the Tables design view and created an Autonumber field. That was the smooth part. To create different fields in VBA see the Create Field in VBA example.

MS Access Autonumber field. 

I then proceeded to close and save the table. It was then that MS Access gave me the dreaded "Not Enough Space on Temporary Disk" error.

MS Access Not Enough Space on Temporary Disk Error 

I really needed the Autonumber field so I manually created a table with similar fields, Set my Autonumber field and then Appended my data to it. To much work of course but a valid work around none the less. I then researched and made a faster reusable method for adding the Autonumber field via VBA inside a MS Access Form.

I have made the download for this VBA code available here In a sample MS Access database. The VBA uses both a Form and a Module. The VBA code targets one empty table. Click the picture below to see a larger version.

MS Access Form 

Usage is free of course. Actual Usage: Simply Drop the module and Form in any MS access database with a Table named "1A". You can right click tables and rename them. Click the forms Menu and then double click the AUTONUMBER_FIELD Form. You will be presented with a grey form with a Button named Execute. Click the button and ignore the "Not Enough Space on Temporary Disk" error as you create your custom Autonumber.

MS Access AutoNumber Complete. 

If the fields the form targets has already been played with the Form is designed to throw an error. These errors are just to let you know something is amiss. Then it will complete the process as the above picture illustrates.

MS Access Form Errors. 

The Acual VBA Code for the Create Autonumber Field, you should be able to copy/paste right into a module:

CreateAutoNumberField( _
ByVal strTableName As String , _
ByVal strFieldName As String ) _
As Boolean

'References: Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library
   'Set references by Clicking Tools and Then References in the Code View window
   'Creates an Autonumber field 
   ' strTableName : Name of table in which to create the field 
   ' strFieldName : Name of the new field
   'Returns True on success, false otherwise 
   'USAGE: CreateAutoNumberField "TABLENAME", "FIELDNAME" 

On Error GoTo errhandler 

Dim Db As DAO.Database
Dim fld As DAO.Field
Dim tdf As DAO.TableDef 

Set Db = Application.CurrentDb 
Set tdf = Db.TableDefs(strTableName) 

' First create a field with datatype = Long Integer
Set fld = tdf.CreateField(strFieldName, dbLong) 

With fld
' Appending dbAutoIncrField to Attributes
      ' tells Jet that it's an Autonumber field
.Attributes = .Attributes Or dbAutoIncrField
End With 

      .Append fld
End With
CreateAutoNumberField = True

Set fld = Nothing
tdf = Nothing
Db = Nothing

MsgBox "Autonumber Complete"
Exit Function

CreateAutoNumberField = False 

"Error " & .Number & vbCrLf & .Description, _
Or vbCritical, "CreateAutonumberField"
End With 


End Function

For the above and a few other functions download the Sample.

You may post this tutorial on your website or in a forum. If you do please maintain a Link to Eraseve AP.

For further MS Access tutorials go here: MS Access tutorials

To Create an Autonumber field manually go here.

Go here To Check if Table exists in VBA.
Go here To Check if Field exists in VBA.
Go here To Update a Field in VBA
Go Here to Change a field’s data type in VBA


Did you find what you were looking for?
What would you suggest?
Your Name:
Your Message:
Your Email:



Custom Search