In this article I will explain how you can check if a field exists in an access database using VBA.

Previously in the article below I’ve explained how you can get the name of all the fields in a table in an access database using VBA:

• Access VBA Get Field Names

I will be using the code in that article here.

Note: The example database used in this article has a table with the name “MyTable1“.

Note: In order to work with the Recordset object you may need to add reference to the Microsoft ActiveX Data Object Library. This has been explained in the article below:

• VBA Access Recordset, Object Library

The function below receives as input the name of a table and field. It checks if the field exists in the table:

Function CheckExists(ByVal strField As String) As Boolean
Dim objRecordset As ADODB.Recordset
Dim i As Integer

Set objRecordset = New ADODB.Recordset
objRecordset.ActiveConnection = CurrentProject.Connection
objRecordset.Open ("MyTable1")
'loop through table fields
For i = 0 To objRecordset.Fields.Count - 1
    'check for a match
    If strField = objRecordset.Fields.Item(i).Name Then
        'exist function and return true
        CheckExists = True
        Exit Function
    End If
Next i
'return false
CheckExists = False
End Function


It can be tested using the code below:

Sub test()
If CheckExists("MyField1") = True Then
    MsgBox ("Field exists")
Else
    MsgBox ("Field does not exist")
End If
End Sub


Result:

Note: If you do not wish to add reference to the Microsoft ActiveX Data Object Library you could use late binding to initiate the recordset object:

Dim objRecordset As Object 
Set objRecordset = CreateObject("ADODB.Recordset")

You can download the file and code used in this article from the link below:

• Check If Field Exists.Zip

See also:

• Access VBA Get Field Names
• Check If Table Exists Access VBA
• VBA Access  Get Table Names

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com