First Published 17 Jan 2025


Collections

Access groups all database objects in collections. These can be viewed by querying the MSysObjects system table:

SELECT Name AS Collection, Type, Id
FROM MSysObjects
WHERE Type=3;

With a few exceptions, it should be obvious what each collection holds. However:
a)   There is no Queries collection. The Tables collection contains both tables and queries.
      These are distinguished by the Type value: 5 for queries, 1, 4 or 6 for tables (local, linked ODBC, linked Access etc).

b)   For historical reasons, Macros are stored in the Scripts collection.

c)   DataAccessPages is a legacy collection for the Page objects available in Access 2000-2003 MDB files but deprecated in the ACCDB file format

d)   The Databases and SysRel collections can be ignored here. These hold items used by Access for managing databases and their contents are not accessible to developers.



Object Types

Access manages the object types by referencing the ParentId field for each object.
To see how this works make a query using two copies of the MSysObjects table (aliased below as MSO & MSO1) with a self join.
The ParentId field in one table is joined to the Id field in the other and used to reference the collection.


The query SQL is shown below

SELECT Switch([MSysObjects].[Type] In (1,4,6),"Table",[MSysObjects].[Type]=5,"Query",[MSysObjects].[Type]=8,"Relationship",
      [MSysObjects].[Type]=-32768,"Form",[MSysObjects].[Type]=-32761,"Module",[MSysObjects].[Type]=-32766,"Macro",
      [MSysObjects].[Type]=-32764,"Report",[MSysObjects].[Type]=-32756,"Page") AS ObjectType,
      MSO.Name, MSO1.Name AS Collection, MSO.Type, MSO.Flags
FROM MSysObjects AS MSO
      INNER JOIN MSysObjects AS MSO1 ON MSO.ParentId = MSO1.Id
WHERE Switch([MSysObjects].[Type] In (1,4,6),"Table",[MSysObjects].[Type]=5,"Query",[MSysObjects].[Type]=8,"Relationship",
      [MSysObjects].[Type]=-32768,"Form",[MSysObjects].[Type]=-32761,"Module",[MSysObjects].[Type]=-32766,"Macro",
      [MSysObjects].[Type]=-32764,"Report",[MSysObjects].[Type]=-32756,"Page") Is Not Null
ORDER BY Switch([MSysObjects].[Type] In (1,4,6),"Table",[MSysObjects].[Type]=5,"Query",[MSysObjects].[Type]=8,"Relationship",
      MSysObjects].[Type]=-32768,"Form",[MSysObjects].[Type]=-32761,"Module",[MSysObjects].[Type]=-32766, "Macro",
      [MSysObjects].[Type]=-32764,"Report",[MSysObjects].[Type]=-32756,"Page"), MSO.Name;

I have used a Switch function to determine the object type based on the Type field. Alternatively, the query SQL could be shortened by using a lookup table for object types.

The query results will be similar to this:



Further Analysis

Querying other system tables such as MSysComplexColumns, MSysRelationships and MSysQueries, either on their own or in conjunction with the MSysObjects table will allow you to analyze many other features of your database structure.

Details are supplied in several other articles elsewhere on this website. You can use the Site Search feature or the Site Map to look for other articles of interest

I make extensive use of several system tables together with (a lot of) VBA code to do detailed analyses of objects, properties (and more) in my Database Analyzer Pro commercial application.



Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 17 Jan 2025

Return to Code Samples Page

Return to Top