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