DMW logo

How to List Microsoft® Access Database Objects Using a Query

On these other pages are VBA functions that you can use to output lists of objects into the Immediate Window of Access's Visual Basis Editor:

To avoid VBA, the article on this page describes how to use a query to list table objects in a datasheet.

Last updated by David Wallis on 2024-04-25.

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 doesn't 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,




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.


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