VBA Get Table Names in External Access Database (Automation)

In this article I will explain how you can get the names of all the tables in an external access database using VBA.

The first step would be to connect to an external database. I’ve explained this in the article below:

Note: The examples in this article will be provided for 2 methods of automation:

  1. Late binding
  2. Early binding

In the latter method we add reference to the Access Object Library, before execution. It will run faster and we will have access to the VBA editor intellisense. On the other hand there is always the risk of compatibility issues arising when the program is run on a computer with a different version of Access installed. For more information about early vs late binding please see the link below:

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


Get Table Names, In External Access Database:

The example codes 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

Late Binding:

Sub Example1()
'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
'get the tables name
Cells(intRow, 1) = objCatalog.Tables.Item(i).Name
intRow = intRow + 1
End If
Next i
End Sub

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 ActiveX Data Object Library
  • Microsoft ADO Ext. for DDL and Security

Reference

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:

Sub Example2()
'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
'get the tables name
Cells(intRow, 1) = objCatalog.Tables.Item(i).Name
intRow = intRow + 1
End If
Next i
End Sub

Result:
As you can see in the figure below the name of the tables in the access database have been printed in column A of the excel worksheet:
Result


Code Explanation:

The code below opens the access database located in the path “D:StuffBusinessTemp” under the name “NewDB.accdb” and gets its connection string:

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

The code below creates an ADODB connection with the connection string provided by the code above:

'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

The code below creates a catalog object. The name of the tables are retrieved from this object:

'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

If you open the catalog object in the watch window you can see the object contains a Table collection. Information regarding the tables in the access database can be retrieved from this collection:
Table Collection

As you can see in the figure above there are 15 tables in that collection, while only 3 tables have been defined in the database. Not all tables in the Table collection are user defined. Some tables are system tables. You can check whether a table is user defined or not by checking the “TYPE” property of the table:
Type

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

 

Leave a Reply

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

privacy policy