MsysObjects is one of the Access "System Tables". These tables are used to keep track of objects in the database.

(NOTE: this article was written based on research using Access 2000. It may or may not work on other versions of these programs. Since MsysObjects is a system table, Microsoft may change it in a future version of Access.)

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

To see the data in MsysObjects, try the following:

• open an existing database with a variety of objects
• start a new query design
• cancel the addition of a table
• go to the SQL option in the top left corner of the screen
• type in the next line
• Select Name, Type from MsysObjects Order By Type, Name;
• run the query
• examine the resulting data

You should recognize the names of your tables, forms, queries, reports, and modules. Note the type codes.

• Table = 1
• Query = 5
• Form = -32768
• Report = -32764
• Module = -32761

Now we can use this info in a query. For example, to offer a drop down of all the reports in your database, the following can be placed as the record source for the combo box:

(article continues after sponsor spot)

Recently I had a requirement to have a task done just once, the first time the database was opened. Here is what I did:

• created a small "dummy" table called "tblInitialize"
• made the Main Menu form start automatically
• added the following code to the Form Open event of the Main Menu

If the dummy table exists, then process some instructions. If the table is not there, assume the process has already been completed.

"Gotchas"

Unfortunately, the above is somewhat simplified, especially for tables.

If your database has links to tables in other mdb files, or ODBC databases, then the Type code for them will be 4 (ODBC) or 6 (linked mdb).

Access also uses a "~" or "Msys" to name objects it creates on its own. Some authors recommend checking for these special cases whenever you are looking at all the objects of any type. Eg. "Select Name, Type from MsysObjects Where left(name,1)<>'~' and left(name,4)<>'Msys' Order By Type, Name;".

Further ideas:

Try this version of the initial query on MsysObjects:
   Select * from MsysObjects Order By Type, Name;
Check out the other fields and determine their usefulness, especially if you have linked tables.

Happy Coding

 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.
 

© 2006, 2007 Richard W. Killey. All Rights Reserved.