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

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

• VBA Access  Get Table Names

I will be using the code in that article here.

The access database used for the example in this article has 3 tables:

1. Table1
2. Table2
3. Table3

Note: The code below uses the ADOX.Catalog object. In order to use this object you may need to add reference to its object library. For more information please see the link below:

• VBA Access ADOX.Catalog, Object Library

The function below receives as input the name of a table and checks if the table exists in the access database:

Function CheckExists2(ByVal strTable As String)
Dim arrTables(1 To 100) As String
Dim objCatalog As ADOX.Catalog
Dim i As Integer

Set objCatalog = New ADOX.Catalog
'connect catalog object to database
objCatalog.ActiveConnection = CurrentProject.Connection
'loop through the tables in the catalog object
For i = 0 To objCatalog.Tables.Count - 1
    'checks if the table is a user defined table
    If objCatalog.Tables.Item(i).Type = "TABLE" Then
        If strTable = objCatalog.Tables.Item(i).Name Then
           CheckExists2 = True
           Exit Function
        End If
    End If
Next i
CheckExists2 = False
End Function


It can be tested using the code below:

Sub test2()
If CheckExists2("Table1") = True Then
    MsgBox ("Table Exists")
Else
    MsgBox ("Table Doesn't Exist")
End If
End Sub


Result:

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

• Check If Table Exists.Zip

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