Use System Tables to Manage Objects

Friday Aug 19th 2005 by Danny Lesandrini
Share:

Learn how to use Access system tables (Msys-tables) 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.

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?

Putting Tables To Work

Now let's see how we can put these tables to work managing our objects. The plan is simple:

1. Get the list of objects in the local database

2. Get the list of objects in the User Objects database

3. Compare the two lists, filtering out objects you want to ignore

That last point is important. My databases often get large, with lots of objects and I do not want to have to put a copy of each object in my user object repository database. So, to purge the final list of "new" objects of all my standard client pieces, a table was populated with a list of objects to ignore. The result is what you see in the image below, frmDemo, which displays a subset of objects that qualify as "new" or user created. The demo code, which is available for download, comes with a single "new" query named qryGreaterThanTen. The best way to understand this code is to play with it yourself. It is relatively self-contained and the few pieces can be imported into any database and used with little or no modification.

Click for larger image

What is shown above is the list of new objects that appear in the client database, which have not been saved to the UserObjects file. The other tab shows the opposite: objects saved to the user's object file, which are not in the current version of the client. Therefore, by using the two system tables, the one in the client mdb and the other in the UserObjects mdb, you can manage the list of which "new" objects are where. However, there is still the little matter of how to move them from one place to another.

Processing Selected Objects

The form contains two buttons, one to Save local objects to the remote (cmdSave) and one to retrieve remote objects back into the current client mdb (cmdRetrieve). The code for these button clicks is relatively simple and the real work is passed to a function. However, I often forget how to loop through selected items in a listbox, so I will reproduce the code here for posterity.

Private Sub cmdRetrieve_Click()
   On Error GoTo Err_Handler
   
   Dim intIItem As Integer
   Dim varVItem As Variant
   
   For Each varVItem In Me!lstRetrieveObjects.ItemsSelected
      RetrieveUserObjects lstRetrieveObjects.Column(0, varVItem), lstRetrieveObjects.Column(1, varVItem)
   Next
   Me!lstRetrieveObjects.Requery
   
Exit_Here:
   Exit Sub
Err_Handler:
   MsgBox Err.Description
   Resume Exit_Here
End Sub
Private Sub cmdSave_Click()
   On Error GoTo Err_Handler
   
   Dim intIItem As Integer
   Dim varVItem As Variant
   
   For Each varVItem In Me!lstSaveObjects.ItemsSelected
      SaveUserObjects lstSaveObjects.Column(0, varVItem), lstSaveObjects.Column(1, varVItem)
   Next
   Me!lstSaveObjects.Requery
   
Exit_Here:
   Exit Sub
Err_Handler:
   MsgBox Err.Description
   Resume Exit_Here
End Sub


The real work is done by the two functions, RetrieveUserObjects() and SaveUserObjects().

Private Sub RetrieveUserObjects(ByVal sType As String, sName As String)
   On Error GoTo Err_Handler
   
   Dim sSource As String
   Dim lType As Long
   
   strSSource = CurrentProject.Path & "\UserObjects.mdb"
   If Dir(sSource) = "" Then
      Exit Sub
   End If
   
   Select Case sType
      Case "Table"
         lType = acTable
      Case "Query"
         lType = acQuery
      Case "Form"
         lType = acForm
      Case "Report"
         lType = acReport
      Case "Macro"
         lType = acMacro
      Case "Module"
         lType = acModule
   End Select
   
   DoCmd.TransferDatabase acImport, "microsoft access", sSource, lType, sName, sName
   
Exit_Here:
   Exit Sub
Err_Handler:
   MsgBox Err.Description
   Resume Exit_Here
End Sub
Private Sub SaveUserObjects(ByVal sType As String, sName As String)
   On Error GoTo Err_Handler
   
   Dim sTarget As String
   Dim lType As Long
   
   sTarget = CurrentProject.Path & "\UserObjects.mdb"
   If Dir(sTarget) = "" Then
      Exit Sub
   End If
   
   Select Case sType
      Case "Table"
         lType = acTable
      Case "Query"
         lType = acQuery
      Case "Form"
         lType = acForm
      Case "Report"
         lType = acReport
      Case "Macro"
         lType = acMacro
      Case "Module"
         lType = acModule
   End Select
   
   DoCmd.CopyObject sTarget, sName, lType, sName
   
Exit_Here:
   Exit Sub
Err_Handler:
   MsgBox Err.Description
   Resume Exit_Here
End Sub

Both functions require a text argument to identify the type of object and in both cases, this text value is converted into an Access numeric constant corresponding to the type. The CopyObject method of the DoCmd object is called to save a local object to a remote database but the TransferDatabase method is used to retrieve objects. Truth be told, I cannot remember the reason for this, but I do remember struggling for a long time and feeling dismayed that the code could not be more similar for both processes. It would have been elegant to use a single function and a single method of the DoCmd object to accomplish both processes, but alas, it wasn't that simple.

However, this code was originally written in Access 97 and while it works in later versions, I never went back to see if a cleaner solution exists. As usual, if anyone knows the answer to this question, or has the time to investigate, I would be interested to know if one or the other of these methods may now be used for both directions of object transfer.

WrapUp

The research for this code originated with a tool I created named Copy Wizard which would analyze the objects in two separate mdb files and allow you to find and copy missing objects from one to the other. As mentioned, it was written in Access 97 and served me well for years. Recently, however, I stumbled on the idea presented above for allowing users to maintain copies of their custom objects, and with a few modifications and a new user form, the task was easily accomplished.Download either of these tools and poke around in the code. If you develop with Microsoft Access, eventually this code will come in handy.

» See All Articles by Columnist Danny J. Lesandrini

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved