dmw | consultancy
tel 01732 833085
tonbridge · kent · UK

VBA Functions to List Access Database Objects

On this page are VBA functions that list the tables, queries, forms, reports and modules in the current database.

DMW uses these functions during the development phases of Access 2007, 2010, 2013 and 2016 databases.

Each of these functions is intended for running in the Immediate Window of the Visual Basic Editor where it will ouput its list of objects.

Last updated on 2018-11-23 by David Wallis.


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

Comments

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.


VBA Function to Exclude System Tables

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 dmwLinkedTables() 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:

dmwLinkedTables = msg$

Exit Function

errHandler:

msg$ = Err.Number & " " & Err.Description

Resume procDone

End Function


Give DMW’s Tips Your Support

ContributionIf you intend making use of Tips in any of your professional or commercial work and would like to make a donation in recognition of the time I spend publishing them, then please click Contribution and tell me how much you wish to donate. In response I’ll email you an invoice for payment by PayPal or card.

Thanks, in anticipation.


VBA Function to List All Queries

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

Function dmwListAllQueries() As String

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

On Error GoTo errHandler

Dim msg$

Dim qry As AccessObject, dB As Object

Set dB = Application.CurrentData

For Each qry In db.AllQueries

Debug.Print qry.Name

Next qry

msg$ = "Queries listing complete"

procDone:

dmwListAllQueries = msg$

Exit Function

errHandler:

msg$ = Err.Number & " " & Err.Description

Resume procDone

End Function


VBA Function to List All Forms

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

Function dmwListAllForms() As String

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

On Error GoTo errHandler

Dim

Dim frm As AccessObject, dB As Object

Set dB = Application.CurrentProject

For Each frm In db.AllForms

Debug.Print frm.Name

Next frm

msg$ = "Forms listing complete"

procDone:

dmwListAllForms = msg$

Exit Function

errHandler:

msg$ = Err.Number & " " & Err.Description

Resume procDone

End Function


VBA Function to List All Reports

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

Function dmwListAllReports() As String

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

On Error GoTo errHandler

Dim msg$

Dim rpt As AccessObject, dB As Object

Set dB = Application.CurrentProject

For Each rpt In dB.AllReports

Debug.Print rpt.Name

Next rpt

msg$ = "Reports listing complete"

procDone:

dmwListAllReports = msg$

Exit Function

errHandler:

msg$ = Err.Number & " " & Err.Description

Resume procDone

End Function


VBA Function to List All Modules

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

Function dmwListAllModules() As String

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

On Error GoTo errHandler

Dim msg$

Dim obj As AccessObject, db As Object

For Each obj In db.AllModules

Debug.Print obj.Name

Next obj

msg$ = "Module listing complete"

procDone:

dmwListAllModules = msg$

Exit Function

errHandler:

msg$ = Err.Number & " " & Err.Description

Resume procDone

End Function


How to Run Your Function

To execute the function, copy and paste the code 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.


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.

“Lists are how I parse and manage the world.”

Adam Savage (American entertainer)