In this article I will explain how you can check if a field exists in an external 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 external access database using VBA:

• Get Field Names in External Access Database, VBA Automation

I will be using the code in that article here.

Note: The examples used in this article are based on the following assumptions:

1. The access database is located in the path “D:StuffBusinessTemp”.
2. The name of the access database is “NewDB.accdb”.
3. It is assumed the database has a table with the name “MyTable1”.

Note: Although the code in this article was written in the Excel VBA editor, it can be used in any office application with VBA.

Late Binding:

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

Function CheckExists1(ByVal strTable As String, _
ByVal strField As String) As Boolean
'an Access object
Dim objAccess As Object
Dim objRecordset As Object
Dim i As Integer

Set objAccess = CreateObject("Access.Application")
'open access database
Call objAccess.OpenCurrentDatabase( _
"D:StuffBusinessTempNewDB.accdb")
'get tables data
Set objRecordset = _
objAccess.CurrentProject.Connection.Execute(strTable)
'loop through table fields
For i = 0 To objRecordset.Fields.Count - 1
    'check for a match
    If objRecordset.Fields.Item(i).Name = strField Then
        'exit function with the value True
        CheckExists1 = True
        Exit Function
    End If
Next i
'exit function with the value false
CheckExists1 = False
End Function


It can be tested using the code below:

Sub test1()
If CheckExists1("MyTable1", "MyField1") = True Then
    MsgBox ("Field Exists")
Else
    MsgBox ("Field Does not exist")
End If
End Sub


Result:

Early Binding:

In early binding you will need to add reference to the libraries below. This can be done in the VBA editor (Tools>>Reference):

• Microsoft Access Access Object Library
• Microsoft ADO Ext. for DDL and Security

Note: The versions of the libraries mentioned above installed on my computer might be different than what is installed on your computer. This will not effect the code.

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

Function CheckExists2(ByVal strTable As String, _
ByVal strField As String) As Boolean
'an Access object
Dim objAccess As Access.Application
Dim objRecordset As ADODB.Recordset
Dim i As Integer

Set objAccess = New Access.Application
'open access database
Call objAccess.OpenCurrentDatabase( _
"D:StuffBusinessTempNewDB.accdb")
'get tables data
Set objRecordset = _
objAccess.CurrentProject.Connection.Execute(strTable)
'loop through table fields
For i = 0 To objRecordset.Fields.Count - 1
    'check for a match
    If objRecordset.Fields.Item(i).Name = strField Then
        'exit function with the value True
        CheckExists2 = True
        Exit Function
    End If
Next i
'exit function with the value false
CheckExists2 = False
End Function


It can be tested using the code below:

Sub test2()
If CheckExists2("MyTable1", "MyField1") = True Then
    MsgBox ("Field Exists")
Else
    MsgBox ("Field Does not exist")
End If
End Sub


Result:

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

• Check If Field Exists.xlsm

Note: Although the code was written in the Excel VBA Editor, it can be written in any other office application.

See also:

• Get Field Names in External Access Database, VBA Automation
• Check If Table Exists in External Access Database, VBA Automation
• Get Table Names from External Access Database, VBA Automation
• How to use Exit Function to end function execution

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