How to List Microsoft® Access Database Objects

Last updated on 2022-09-01 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:

On those pages, VBA functions are used to list the objects in question. To avoid VBA, this article describes how to use a query to list table objects.

Query for Listing Database Tables

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

Query for listing tables in an Access database

Running the query delivers this datasheet:

Datasheet of tables in an Access database

The first column, Database, identifies the path to the database file that contains the tables. Usually, this is the back end (BE) of a split database.

The second column, ForeignName, identifies the name of the table in the database BE.

The third column, Name, is the name by which the table is known in the database in which the query is running. Usually this database will be the front end (FE) of a split database.

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 this 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));

The WHERE (((MSysObjects.Type)=6)) condition serves to limit the output of the query to linked tables. Remove this condition and your query will list a host of other objects including forms and reports.

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.