How to List Access Database Objects in Microsoft Access
Question details
The user needs to generate a comprehensive list of all database objects, such as tables, queries, forms, reports, modules, and macros stored within a Microsoft Access database.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Auditing or reviewing the structure of a database file to extract a complete list of all object names and their respective types.
- Observed behavior
- Looking for a method or SQL script to systematically output all database objects while cleanly filtering out hidden system and temporary files.
Ensure you have the necessary database permissions to read hidden system objects like the MSysObjects table, as restricted permissions may prevent the query from running properly.
Run a SQL UNION Query Against MSysObjects
Use a custom SQL query targeting the hidden MSysObjects system table to extract a clean list of all user-created database objects while excluding temporary files.
Microsoft Access automatically stores metadata for tables, queries, forms, reports, modules, and macros in a system table called MSysObjects. You can use a UNION query to retrieve and group these object names alongside their types.
The object type values used in MSysObjects are: 1 for tables, 5 for queries, -32768 for forms, -32764 for reports, -32761 for modules, and -32766 for macros.
Open your Microsoft Access database, navigate to the 'Create' tab on the ribbon, and click on 'Query Design'. If the 'Show Table' dialog box appears, simply close it.
Right-click the query tab or use the 'View' button in the top-left corner to switch your query window from Design View to 'SQL View'.
Paste the following SQL statement into the text area: SELECT MyList.ObjTyp, MyList.Name FROM (SELECT Name, 'table' AS ObjTyp FROM MSysObjects WHERE [Name] NOT LIKE '~*' AND [Name] NOT LIKE 'MSys*' AND MSysObjects.Type = 1 UNION SELECT Name, 'query' AS ObjTyp FROM MSysObjects WHERE [Name] NOT LIKE '~*' AND MSysObjects.Type = 5 UNION SELECT MSysObjects.Name, 'form' AS ObjTyp FROM MSysObjects WHERE [Name] NOT LIKE '~*' AND MSysObjects.Type = -32768 UNION SELECT MSysObjects.Name, 'report' AS ObjTyp FROM MSysObjects WHERE [Name] NOT LIKE '~*' AND MSysObjects.Type = -32764 UNION SELECT MSysObjects.Name, 'module' AS ObjTyp FROM MSysObjects WHERE [Name] NOT LIKE '~*' AND MSysObjects.Type = -32761 UNION SELECT MSysObjects.Name, 'macro' AS ObjTyp FROM MSysObjects WHERE [Name] NOT LIKE '~*' AND MSysObjects.Type = -32766) AS MyList GROUP BY MyList.ObjTyp, MyList.Name ORDER BY MyList.ObjTyp DESC;
Click the 'Run' button (the red exclamation mark icon) in the Design ribbon to execute the query. The results will display a clean, categorized list of your database objects.
Use the Access Database Documenter
Utilize the built-in Database Documenter tool to automatically generate a detailed report of your database objects without writing any SQL.
Looking for a Lightweight Microsoft Office Alternative?
While WPS Office does not include a direct database tool like Microsoft Access, it is an exceptionally robust, free alternative for your everyday word processing, spreadsheet analysis, and presentation tasks. It offers a highly familiar UI, lightweight installation, and seamless migration for standard office document workflows.
- 1. Visit the Official Website: Navigate to the WPS Office website to find the version that matches your PC or Mac operating system.
- 2. Download the Installer: Click the download button to grab the lightweight installation package.
- 3. Install and Begin Working: Run the quick installer, open the application, and start editing your documents with zero learning curve.
Frequently Asked Questions
What is the MSysObjects table in Access?
MSysObjects is a hidden, built-in system table in Microsoft Access that automatically stores essential metadata about all the database objects (such as tables, queries, forms, and reports) that exist within the database file.
Why do some database objects start with '~' in Access?
Object names prefixed with a tilde ('~') are generally temporary files created by Microsoft Access during database operations, or they represent objects that have been deleted but not yet purged by compacting the database.
How can I view hidden system tables in the Navigation Pane?
To view hidden system objects, right-click the top header of the Navigation Pane, select 'Navigation Options', and check the box labeled 'Show System Objects' under the Display Options.
