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“.
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
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
2 thoughts on “Check If Field Exists, VBA Access”