Listing all Access tables in your database

In your Access application, you may have a need to list all of the tables in your database.

For example, you may want to run a query on all of the tables, or want to display the table names in a list box for the end user to select from.

You don't, however, want to go through the trouble of hard coding table names into your procedure. Every time you add, remove or rename a table, you have to remember to update the list of tables. Very inconvenient.

You don't care how many tables there are, you want all of them. Enter this procedure: it returns a string array of visible table names!

First we get a count of all the tables, then loop through the TableDefs collection and subtract one for each invisible table. We have to do this because there are invisible system tables we don't need or want to edit.

So if you have 20 tables, but only 15 of them are visible (according to the list of tables in the Objects menu), the first loop will cut the count down to 15. Then we redimension our tables list to that size.

The second loop will again loop through the visible tables and add them to the string array, which is now exactly the right size for the number of visible tables we have in our database.

Function GetTableList() As String()
' returns string array of visible table names

  Dim tbl As DAO.TableDef
  Dim tables As DAO.TableDefs
  Dim tablesCount As Long
  Dim tablesList() As String
  Dim i As Long

  ' get tables count and resize temp array
  tablesCount = CurrentDb.TableDefs.Count

  ' remove invisible tables
  Set tables = CurrentDb.TableDefs
  For Each tbl In tables
    If tbl.Attributes <> 0 Then ' it's visible
      tablesCount = tablesCount - 1
    End If
  Next tbl

  ' resize array to number of visible tables
  ReDim tablesList(1 To tablesCount)

  ' start index at one to match array lbound
  i = 1

  For Each tbl In tables
    If tbl.Attributes = 0 Then
      tablesList(i) = tbl.Name
      i = i + 1
    End If
  Next tbl

  GetTableList = tablesList

End Function

Sample usage:

Dim tableList() As String
tableList = GetTableList