Projects	Perfect People	Tips N Tricks	Find Out More	Write To Us

Using MSysObjects

MS Access System Objects consist of several tables that give you important and useful information about your database. One such Table is the MSysObjects table. This gives comprehensive information about all your database objects (i.e. queries, forms etc.) that can come in very handy when you document your database. To obtain access to system objects set View/Options/ General/Show System objects to "yes".

A very useful feature of MSysObjects that should not be ignored is the information that the columns DateCreate and DateUpdate supply on your database objects. The DateUpdate can be used in the creation of a "Last Updated" query that can tell you what modules, forms or reports have been modified since a particular date or within a specific period.

We use the 'LastUpdated' Query on a regular basis when we are updating our clients' databases with new or changed objects. Create a New Query and copy and paste the SQL statement below to generate a list of new / changed objects.

SELECT MSysObjects.DateCreate, MSysObjects.DateUpdate,
MSysObjects.Name, MSysObjects.Type
FROM MSysObjects;

Save this Query as 'qryLastUpdated'. You can then add your own criteria for DateUpdate or DateCreate in the QBE grid viz:

SELECT MSysObjects.DateCreate, MSysObjects.DateUpdate, 
MSysObjects.Name, MSysObjects.Type 
FROM MsysObjects WHERE
(((MSysObjects.DateCreate)>=#1/3/97#));

OR

SELECT MSysObjects.DateCreate, MSysObjects.DateUpdate, 
MSysObjects.Name, MSysObjects.Type
FROM MsysObjects 
WHERE (((MSysObjects.DateUpdate) Between #1/1/97# And #1/5/97#));

If the values in the column Type faze you, just refer to our ready reckoner for MSysObjects.Type given below. This contains the list of values that are commonly used by developers and are consistent over MS-Access versions 2.0, 7.0 and 8.0

A word of caution: When you play with any MS Access system table, use extreme caution. Unintentional or intentional modifications of any kind in these tables could result in an unusable database. Always take a backup of your database before editing any values in the MSysObjects and other system tables.

Click here to download a sample database (7.8KB) with qryLastUpdated Queries in MS-Access 2.0. (You can convert this database to the version you are using.)

More Resources on MSysObjects:

Does this object exist in my database? Look in MSysObjects table.
Resource: http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B90989

Related queries:
Does this form exists in my database. MSysObjects has the answer.
Resource: http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B92825

Can I change the layout of my MS Access system tables?
Resource: http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b174090

Why does my LastUpdated Property return an incorrect Date/Time? (Access 2000)
Resource: http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B299554

System Objects Retain their Properties no matter what I do. This is not a bug!
Resource: http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b94597

Disclaimer: All information is provided on an "as is" basis. No guarantees or warranties are made or supplied or intended. You are free to use any code published on this page; however, the risk is entirely yours.