How to List All Tables in an Access Database
VBA functions to list in the Immediate Window of its Visual Basic Editor all the tables of a Microsoft® Access database.
Last updated on 2024-05-16.
VBA Function to List All Tables
This function lists all the tables in the database in which you execute it.
Function fnDmwListAllTables() As String
On Error GoTo errHandler
Dim tbl As AccessObject, db As Object
Dim msg$
Set dB = Application.CurrentData
For Each tbl In db.AllTables
Debug.Print tbl.Name
Next tbl
msg$ = "Tables listing complete"
procDone:
fnDmwListAllTables = msg$
Exit Function
errHandler:
msg$ = Err.Number & " " & Err.Description
Resume procDone
End Function
Note that in the list produced may be names of tables you don't recognise. These are system tables which are dealt with in the next section.
fnDmwListAllTables() will not distinguish between native and linked tables. Listing linked tables only is dealt with below.
VBA Function to Exclude System Tables
Note that the list of tables that fnDmwListAllTables() generates includes the names of system tables that are usually hidden from the database user's view. Such tables have names beginning with MSys.
This function lists the tables in your database but in so doing excludes from the list the names of database systems tables - those tables beginning “MSys”.
The difference in code between fnDmwListAllTablesNotMSys() and fnDmwListAllTables() is within the For...Next tbl Loop:
Function fnDmwListAllTablesNotMSys() As String
On Error GoTo errHandler
Dim tbl As AccessObject, dB As Object
Dim msg$
Set dB = Application.CurrentData
For Each tbl In db.AllTables
If Not Left(tbl.Name, 4) = "MSys" Then
Debug.Print tbl.Name
End If
Next tbl
msg$ = "Tables listing complete"
procDone:
fnDmwListAllTablesNotMSys = msg$
Exit Function
errHandler:
msg$ = Err.Number & " " & Err.Description
Resume procDone
End Function
VBA Function to List Linked Tables
This function lists linked tables only:
Function fnDmwListLinkedTables() As String
On Error GoTo errHandler
Dim dbs As Object, tblDef As AccessObject
Dim msg$
Dim tblType%
Set dbs = Application.CurrentData
For Each tblDef In dbs.AllTables
tblType% = _
Nz(DLookup("Type", "MSysObjects", _
"Name = '" & tblDef.Name & "'"), 0)
If tblType% = 6 Then Debug.Print tblDef.Name
Next tblDef
msg$ = "Table listing complete"
procDone:
fnDmwListLinkedTables = msg$
Exit Function
errHandler:
msg$ = Err.Number & " " & Err.Description
Resume procDone
End Function
How to Run Your Function
To execute any one of the functions, copy and paste its code shown above into a module in your database's Visual Basic Editor.
Then in its Immediate Window type ?NameOfFunction() and press Enter. Don't omit the leading question mark.
Make a List of Fields in a Table
This page tells you one way to create a list of the fields (columns) in a table and some of the properties of each field: How to List Fields in Access Database Tables.
Your Support for DMW TIPS
Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.
To make a contribution by PayPal in GBP (£ sterling) —
To make a contribution by PayPal in USD ($ US) —
Thanks, in anticipation.
Disclaimer
David Wallis does not accept any liability for loss or damage to data to which any techniques, methods or code included in this website are applied. Back up your data; test thoroughly before using on live data.