Check If Table Exists, Access VBA

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:

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

Tables in Database

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:

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

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

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

Leave a Reply

Your email address will not be published.