How to List Access Tables and ODBC Links Using MSysObjects
Question details
The user wants to extract a list of tables, ODBC links, and other database object metadata from a Microsoft Access database.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Auditing or retrieving metadata for database objects and linked tables within Microsoft Access.
- Observed behavior
- Database object information is not stored as individual files but inside the database, requiring the user to retrieve system information via the hidden MSysObjects table.
Ensure you have proper administrative or read permissions for the Access database, and back up your database file before querying hidden system tables.
Query the MSysObjects System Table
Use a SQL SELECT query to extract detailed information about database objects and ODBC links directly from the hidden MSysObjects system table.
Microsoft Access keeps track of all database objects in a hidden system table called MSysObjects. By running read-only queries against this table, you can retrieve names, object types, creation dates, and connection details for linked tables.
In Access, go to 'File' > 'Options' > 'Current Database' > 'Navigation Options', and check the box for 'Show System Objects' to make the hidden tables visible.
Navigate to the 'Create' tab on the ribbon and click on 'Query Design'. Close the 'Show Table' dialog box without adding any tables, then switch to 'SQL View' using the view toggle at the bottom right.
Type the query: SELECT * FROM MSysObjects WHERE Type = 4; (Note: Type 4 retrieves linked ODBC tables. Use Type 1 for local tables or Type 6 for linked Access tables).
Click the 'Run' button (the red exclamation mark) on the Query Design ribbon to execute the query and view your comprehensive list of tables and object information.
Manage Data Efficiently with WPS Office
While Microsoft Access is built for complex relational databases, WPS Office provides a lightweight, highly compatible, and free alternative to Microsoft Office for your everyday tabular data management, calculations, and document creation needs.
- 1. Download the Software: Visit the official WPS Office website and download the free installation package for your operating system.
- 2. Install WPS Office: Run the downloaded installer and follow the straightforward on-screen instructions to complete the setup.
- 3. Manage Data in Spreadsheets: Launch WPS Spreadsheets to easily import CSV exports from your database, manage tabular data, and utilize advanced formulas.
Frequently Asked Questions
What is the MSysObjects table in Access?
MSysObjects is a hidden system table in Microsoft Access that stores essential metadata about all objects within the database, including tables, queries, forms, reports, macros, and linked ODBC connections.
How do I list only local tables instead of ODBC links?
To list only local tables, modify your SQL query to filter by Type 1 and exclude system tables by checking the Flags column. For example: SELECT Name, DateCreate, DateUpdate FROM MSysObjects WHERE Type = 1 AND Flags = 0;
Why can't I see the MSysObjects table in my navigation pane?
System objects are hidden by default to prevent accidental modifications by users. You can unhide them by right-clicking the top of the Navigation Pane, selecting 'Navigation Options', and checking 'Show System Objects'.
Can I edit the data directly inside MSysObjects?
No. You should never attempt to manually edit, update, or delete records in the MSysObjects table or any other system table. Altering these tables directly will almost certainly result in severe database corruption.
