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:



Function
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 

   With
tdf.Fields
      .Append fld
      .Refresh
  
End With
  
CreateAutoNumberField = True

ExitHere:
  
Set fld = Nothing
   Set
tdf = Nothing
   Set
Db = Nothing

  
MsgBox "Autonumber Complete"
  
Exit Function

errhandler:
  
CreateAutoNumberField = False 

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

   Resume
ExitHere

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?
mail@eraserve.com
Your Name:
Your Message:
Your Email:

    

 

Custom Search