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?
