Querying system objects

How much do you know about MSysObjects? Well, it's a hidden system table that includes information about the contents of your database. As such, you can use it to see if objects exist, when they were created, and so on. These are the key fields in the MSysObjects table:

• Name - the text name of the database object
• DateCreate - the date and time the object was created
• DateUpdate - the date and time the design of the object was last modified
• Connect and Database - used for storing connection information for linked tables
• Type - numeric value indicating the type of object, as follows:
• Form: -32768
• Report: -32764
• Macro: -32766
• Module: -32761
• Local table: 1
• Local database: 2
• Collection (e.g. forms, reports, modules, etc): 3
• Query: 5
• Linked table: 6
• Relationship: 8

So, for example, to return the names of all tables created since 1st January 2001, the SQL would be something like:

SELECT Name
FROM MSysObjects
WHERE Type=1
AND DateCreate>=#01/01/2001#;

Note that a Type value of 5 also returns all SQL queries embedded as the recordsource in forms and reports, as well as standalone queries.