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

Get Table Names:

In the example below the names of the tables will be stored in a string array. The database used in these examples 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:

• VBA Access ADOX.Catalog, Object Library

Sub Example()
Dim arrTables(1 To 100) As String
Dim objCatalog As ADOX.Catalog
Dim i As Integer
Dim intIndex As Integer

Set objCatalog = New ADOX.Catalog
'connect catalog object to database
objCatalog.ActiveConnection = CurrentProject.Connection
intIndex = 1
'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
        arrTables(intIndex) = objCatalog.Tables.Item(i).Name
        intIndex = intIndex + 1
    End If
Next i
End Sub 

Note: If you do not wish to add reference to the library you could initiate the Catalog object using the method below:

Dim objCatalog As Object
Set objCatalog = createobject("ADOX.Catalog")


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

• Get Table Names.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