Check If Field Exists, VBA Access

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:

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

Fields

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:

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

See also:

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

One thought on “Check If Field Exists, VBA Access”

  1. Thomas says:

    Used this bit of code in a project.

    However, I had to modify the following : objRecordset.Open (“MyTable1”)

    To : objRecordset.Open (“SELECT * FROM MyTable1;”)

    Thanks !

Leave a Reply

Your email address will not be published. Required fields are marked *