MS Access VBA Get Number of Fields for versions 2000, 2002, 2003, 2007

Go here To Check if Table exists in VBA.
Go here To Get all Fields in VBA.
Go here To Check if Field exists in VBA.
Follow the Set Autonumber Field with VBA Link to Set a Field to Autonumber.

The VBA Get Number of Fields Sample is available for download here in a zipped MS Access Database. The below picture (click to enlarge) shows the Single form and module.

MS Access VBA Get Number of Fields within a Table.

The following code snippet will use VBA to Return Number of Fields within a given table.

Function GetNumFields( TableName As String) As Long
Dim Db As DAO.Database ' DAO Vars
Dim tdf As DAO.TableDef
Dim LngField As Long

'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
' Returns Number of Fields that exist in Table TableName.
' Accepts
' TableName: Name of table in which the fields are located
' Returns Number of Fields
'USAGE: GetNumFields "TABLENAME"

On Error GoTo errhandler 'If there is no Field capture the error.

Set Db = CurrentDb()

Set tdf = db.TableDefs(TableName)

' Count the fields (columns)

     LngField = tdf.Fields.Count 'Get the number of fields

'Return the number of Fields
GetNumFields = LngField
'If no errors

ExitHere:

Set tdf = Nothing
Set db = Nothing

'Notify the user the process is complete.
MsgBox "Field Count Complete"
Exit Function

errhandler: 'There is an error return as 0
GetNumFields = 0

With Err

             MsgBox "Error " & .Number & vbCrLf & .Description, _
              vbOKOnly Or vbCritical, "GetNumFields"

End With

Resume ExitHere
End Function

For the above VBA Get Number of Fields Function in an usable Form with Button, download the Sample database in ZIP format. The sample includes 100 records and 35 Fields.

For further help or suggestions go here: MS Access tutorials, Tables, Queries and later: Forms

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

Go here To Check if Table exists in VBA.
> Follow the Set Autonumber Field with VBA Link to Set a Field to Autonumber.

Did you find what you were looking for? What would you suggest? mail@eraserve.com


Your Name:
Your Message:
Your Email:

Custom Search