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