DMW logo
tel 01732 833085
tonbridge · kent · UK

VBA Functions to List Tables in
Microsoft® Access Databases

Last updated on 2020-03-24 by David Wallis.

On this page are VBA functions that list all tables, all non-system table and all linked tables in the current database. Each of these functions is intended for running in the Immediate Window of the Visual Basic Editor where it will output the list.


VBA Function to List All Tables

This function lists all the tables in the database in which you execute it.

Function dmwListAllTables() 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:

dmwListAllTables = 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.

dmwListAllTables() 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 dmwListAllTables() 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 dmwListAllTablesNotMSys() and dmwListAllTables() is within the For...Next tbl Loop:

Function dmwListAllTablesNotMSys() 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:

dmwListAllTablesNotMSys = 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 dmwListLinkedTables() 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:

dmwListLinkedTables = 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.


Your Support for dmw TIPS

If you’re going to make use of Tips in any of your professional or commercial work, then please consider making a payment in recognition of the time spent publishing this website.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

To say how much you want to pay and to receive
a tax invoice for a GBP (£ sterling) contribution —

Invoice

Thanks, in anticipation.

SSL Cerification


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