Use System Tables to Manage Objects

August 19, 2005

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.)

Click for larger image

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?








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers