VBA Access Get Table Names

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

Result

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:

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:

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 *