DMW logo
tel 01732 833085
Tonbridge · Kent · UK

How to List Microsoft® Access Database Objects

Last updated on 2021-01-22 by David Wallis.

If you viewed this page before an update on 2020-03-25, then all VBA procedures for listing forms, modules, queries, reports and tables were on this one page. Since then, to give more space to those you’ll find each topic on its own page:

Each function is for inclusion in any of your databases. You execute it in the Immediate Window of the database’s Visual Basic Editor where the function will produce the list.


Query for Listing Database Tables

This is the design view of a query that will list the linked tables in a database:

String test function

Running the query delivers this datasheet:

String test function

The first column, Database, identifies the path to the back-end (BE) file that contains the tables. The second column, ForeignName, identifies the name of the table in the BE.

The third column, Name, is the name by which the table is known in the database in which the query is running.

The query uses the database’s MSysObjects table. Usually, this table does not appear in the list of tables because it is hidden.

To unhide the table so that it appears in the Tables listing, right-click on Tables and pick Navigation options … from the pop-out menu. This will display the Navigation Options dialog. Under Display Options, tick Show System Objects and then OK the dialog box.

Pasting this statement into the SQL View of your query design will create the query for you:

SELECT MSysObjects.Database,

MSysObjects.ForeignName,

MSysObjects.Name,

MSysObjects.Type

FROM MSysObjects

WHERE (((MSysObjects.Type)=6));


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

If you’d like an invoice to account for your donation, let me know how much you’re donating —

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.

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

Adam Savage