DMW logo
contact@consultdmw.com

How to List All Tables in an Access Database

How to create VBA functions to list in the Immediate Window of its Visual Basic Editor all the tables of a Microsoft® Access database.

Last updated on 2021-10-21 by David Wallis.


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 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

DMW Consultancy Ltd 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.

“Use a make-up table with everything close at hand and don't rush; otherwise you'll look like a patchwork quilt.”

Lucille Ball