Check If Table Exists in External Access Database, VBA Automation

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

I will be using the code in that article here.

Note: The examples used in this article assume there is an access database located in the path “D:StuffBusinessTemp” under the name “NewDB.accdb”. It is assumed the database has 3 tables:

  1. Table1
  2. Table2
  3. Table3

Tables in Database

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 checks if the table exists in the access database:

Function CheckExists1(ByVal strTable As String) As Boolean
'an Access object
Dim objAccess As Object
'connection string to access database
Dim strConnection As String
'catalog object
Dim objCatalog As Object
'connection object
Dim cnn As Object
Dim i As Integer
Dim intRow As Integer

Set objAccess = CreateObject("Access.Application")
'open access database
Call objAccess.OpenCurrentDatabase( _
"D:StuffBusinessTempNewDB.accdb")
'get the connection string
strConnection = objAccess.CurrentProject.Connection.ConnectionString
'close the access project
objAccess.Quit
'create a connection object
Set cnn = CreateObject("ADODB.Connection")
'assign the connnection string to the connection object
cnn.ConnectionString = strConnection
'open the adodb connection object
cnn.Open
'create a catalog object
Set objCatalog = CreateObject("ADOX.catalog")
'connect catalog object to database
objCatalog.ActiveConnection = cnn
'loop through the tables in the catalog object
intRow = 1
For i = 0 To objCatalog.Tables.Count - 1
'check if the table is a user defined table
If objCatalog.Tables.Item(i).Type = "TABLE" Then
If objCatalog.Tables.Item(i).Names = strTable Then
Example1 = True
Exit Function
End If
End If
Next i

Example1 = False
End Function

It can be tested using the code below:

Sub Test1()
If CheckExists1("Table1") = True Then
MsgBox ("The table exists")
Else
MsgBox ("The table does NOT exists")
End If
End Sub

Result:
Result


Early Binding

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) As Boolean
'an Access object
Dim objAccess As Access.Application
'connection string to access database
Dim strConnection As String
'catalog object
Dim objCatalog As ADOX.catalog
'connection object
Dim cnn As ADODB.Connection
Dim i As Integer
Dim intRow As Integer

Set objAccess = New Access.Application
'open access database
Call objAccess.OpenCurrentDatabase( _
"D:StuffBusinessTempNewDB.accdb")
'get the connection string
strConnection = objAccess.CurrentProject.Connection.ConnectionString
'close the access project
objAccess.Quit
'create a connection object
Set cnn = New ADODB.Connection
'assign the connnection string to the connection object
cnn.ConnectionString = strConnection
'open the adodb connection object
cnn.Open
'create a catalog object
Set objCatalog = New ADOX.catalog
'connect catalog object to database
objCatalog.ActiveConnection = cnn
'loop through the tables in the catalog object
intRow = 1
For i = 0 To objCatalog.Tables.Count - 1
'check if the table is a user defined table
If objCatalog.Tables.Item(i).Type = "TABLE" Then
If objCatalog.Tables.Item(i).Name = strTable 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 ("The table exists")
Else
MsgBox ("The table does NOT exists")
End If
End Sub

Result:
Result

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

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

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

One thought on “Check If Table Exists in External Access Database, VBA Automation”

Leave a Reply

Your email address will not be published. Required fields are marked *