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.
|
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. 
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.
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.
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.
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 |
