Use System Tables to Manage Objects

A number of months ago I published an article
about how one might auto-update a user’s computer with the latest version of
the client mdb. I use this process with most all of my applications, but
it comes with one downside: the user’s mdb file is overwritten each time
an update is delivered, wiping out any custom queries and reports that might
have been created by power users.

One solution, which will undoubtedly be suggested by some of my more security
conscious readers, is to simply remove from users the ability to create custom
objects, since it is a potential data security issue. While this is true,
it does not really answer the question, and in some installations, that is not
an option. Several of my clients depend upon access to the tables for
custom reporting and I am happy to give them access to their own data, even if
it does open up the possibility of damaging data.

Assuming we are OK with the security issues, how can this matter of disappearing
queries and reports be handled? What follows is a description of how
Access system tables (Msys-tables) may be used to synchronize all database
objects between two databases: one with the user’s custom objects and the
primary client mdb, which continues to be updated and overwritten on a periodic
basis.

System Tables 101

What follows is not intended to be a
comprehensive explanation of the MSYS tables in Microsoft Access but rather,
just enough information to allow you to leverage this valuable resource.
These tables are only visible if and when you set the option, as shown
below. Go to Tools | Options from the main menu and make sure that the
Show System Objects is selected on the View tab. System tables will
display with a slightly dimmed icon, as shown in the image below. (The
same is true of objects you name with the prefix msys … and with the prefix usys,
for that matter.)

The table we are interested in for this task is the MSysObjects table, which
contains a list of every table, query, form, macro and report in our
database. While there are numerous columns in this table, the only ones
that interest us are the Name and Type column. Since the Type field is
actually anumber, I created a work table named wtblObjectType to simplify
the identification of each object, mapping the cryptic number back to a
familiar text label. Below is the result of a query combining the MSysObjects
table with my work table mapping the type ID to a human-readable object name.

MSysObjects Table

Object Type

Type

Name

Form

-32768

frmDemo

Table

1

MSysAccessObjects

Table

1

MSysAccessXML

Table

1

MSysACEs

Table

1

MSysObjects

Table

1

MSysQueries

Table

1

MSysRelationships

Table

1

sales

Table

1

stores

Table

1

titles

Table

1

wtblObjectList

Table

1

wtblObjectType

Query

5

~sq_cfrmDemo~sq_clstAction

Query

5

MSysObjects Table

Query

5

qryGreaterThanTen

Query

5

wqry_LocalObjectList

Query

5

wqry_SavedUserObjects

Query

5

wqry_UnsavedUserObjects

Table

6

wtblUserObjects

Notice that one table shows up with a different type id at the bottom of this
list. The table aliased wtblUserObjects is a linked tableand so its
Type ID is 6, not 1 as with the local tables. Actually, this is a link to
the MSysObjects table in the UserObjects.mdb file that serves as a repository
for savedobjects. More about that later.

For good reason, these system tables are read only. You cannot modify the
values or add/delete rows. However, the up side is that you do not have
to. As you add new tables, queries, forms, etc., this table is updated
automatically. In this way, you have easy access to an inventory of
currently available objects in your database. In fact, the list even
includes the SQL used for the row source of a form or control when it has not
been expressly saved and named. Notice the first query in the above list
is prefaced with a tilde character. This "query" is actually
the unsaved SQL for the lstAction control on the frmDemo form. Pretty
cool, huh?

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Latest Articles