Microsoft Access 2010 Bible

Part III: More-Advanced Access Techniques

740

Because MSysObjects stores the names of all database objects, you can return the names of the

other top-level database objects as well. Just substitute the -32764 as the type value in the preced-

ing SQL statement with the Table 20.3 values to return different database object types.

TABLE 20.3

Microsoft Access Object Types and Values

Object Type Value

Local Tables 1

Linked tables (except tables linked using ODBC) 6

Linked tables using ODBC 4

Forms –32768

Modules –32761

Macros –32766

Queries 5

To view the MSysObjects table, set the Show System Objects setting to Yes in the System

Objects dialog box (which you can get to by right-clicking on the Navigation Pane’s title bar, and

selecting Navigation Options from the shortcut menu). MSysObjects does not have to be

visible for this trick to work.

Note
Although Microsoft has gone on record that MSysObjects and the type values are not supported and are,
therefore, prone to change at any time, Access has used the same type values for many, many years. It’s
unlikely Microsoft will drop the MSysObjects table or change the type values, but this trick is not guaranteed
to work indefinitely.

Fast printing from queried data

A report that is based on a query can take a long time to print. Because reports and forms can’t

share the same recordset, once a user has found the correct record on a form it’s a shame to have to

run the query over again to print the record on a query. A way to “cache” the information on the

form is to create a table (we’ll call it tblCache) containing all the fields that are eventually printed

on the report. Then, when the user has found the correct record on the form, copy the data from

the form to tblCache, and open the report. The report, of course, is based on tblCache.

The query is run only once to populate the form. Copying the data from the form to tblCache is

a very fast operation, and multiple records can be added to tblCache as needed. Because the

report is now based on a table, it opens quickly and is ready to print as soon as the report opens.