Power users create queries and reports which are overwritten each time a new version of the client app rolls out.
Give users the ability to identify, save off and retrieve back their custom database objects.
The principal behind this process is simple, but pay attention to the details described below or you'll get lost. If you understand what is taking place, you will no doubt find other uses for this System Table magic. In a nutshell, here is how the process works:
1. Maintain a table of "official" objects named wtblObjectList
2. Compare that list with MSysObjects to identify 'new' user-created objects.
3. Export those objects to a repository mdb file for safekeeping.
4. Link to the repository MSysObjects table for a catalog of saved objects.
5. Display these objects in UI categories: Save | Retrieve | Update.
6. Execute call to DoCmd.TransferDatabase to Import/Export selected objects.
So, to make this happen, here are the pieces you are going to need:
1. Object list table ... wtblObjectList
2. Repository database ... UserObjects.mdb
3. Query to show objects ready to be saved ... wqry_SaveUserObjects
4. Query to show objects ready to be retrieved ... wqry_RetrieveUserObjects
5. Query to show saved objects available for 'resave' or update ... wqry_ResaveUserObjects
6. Code module to manage the objects ... basManageObjects
My naming convention gets a little kaddywompous here but I couldn't decide if I preferred the idea or 'resaving' an object or 'updating' it. As you can see from the user interface image (above), I settled on Update for the user, even though my application treats it as a 'resave' action. This will make more sense when we look at the code and realize that a resave is actually just a save, with error handling to prevent a warning that the user is about to overwrite changes.
Also, you may note my tables and queries have a 'w' character prefix. This signifies 'work table' or 'work query' and has the added benefit of forcing these tables and queries to the bottom (near bottom) of my object list. I like that because it makes them easy to locate and helps to identify their use. (I have to thank Jim Pilcher of the Denver Area Access User Group for that convention.)
Now that we have all the pieces cataloged, let's look at the code. You can download it and import my forms, queries and tables in your application for a 'quick start' way to implement this functionality.
How The Queries Work
While I could paste the SQL for the queries, the left joins cause it to be difficult to read, especially after Access got finished adding parenthesis, so let's take a visual approach. Below is a screen shot for the Unsaved User Objects query. These are objects that exist in the current database, but have not been saved to the repository.
The central table is the local MSysObjects, which contains the list of ALL objects in our database, including 'temp' queries saved as raw sql in the RecordSource and RowSource properties of forms and controls. They show up in MSysObjects prefixed with the tilde character (~) and we want to filter them out in the WHERE clause.
There is a LEFT JOIN to our local table with the list of all "official" objects and another LEFT JOIN to the MSysObjects table in the repository database. The WHERE clause filters for NULLs with respect to both of these tables. That is to say, we want ALL objects in our local MSysObjects table that do not exist in either our approved list or in the catalog of objects in the repository.
The Saved User Objects query represents things that exist in the repository, but not in the local catalog of objects, nor in our approved list. Accordingly, this query looks a lot like the one above, except the central table is now the MSysObjects table from the repository database, which we have aliased as wtblUserObjects.
A similar sql statement is used to identify objects that exist in both the current database and the repository. It's possible that the user modified a query or report and wishes to update his repository with the latest version. Thus, the LEFT JOIN between the MSysObjects tables is replaced with an INNER JOIN and the NULL filter removed.
When the form opens, it calls a function to check for the existence of the repository database, and if missing, it creates one. Without it, the form cannot load, since all displays and actions depend upon a link to the MSysObjects table in this database.
The tab control contains three listboxes and three buttons that correspond to each of our actions. The code for each button is strikingly similar and offloads the actual processing to the public methods in the module, basManageObjects. Here's a sample of the button code:
Dim varItem As Variant Dim strType As String Dim strName As String For Each varItem In Me!lstSaveObjects.ItemsSelected strType = Nz(lstSaveObjects.Column(0, varItem), "") strName = Nz(lstSaveObjects.Column(1, varItem), "") If strName <> "" And strType <> "" Then SaveUserObject strType, strName End If Next Me!lstSaveObjects.Requery Me!lstUpdateObjects.Requery
Some of the most complicated code for me to remember is how to loop through the selected items in a listbox and process them. Keep this code handy and you'll find a hundred uses for it. If the listbox delivers a non-null object name and type, then those values are passed to the function that does the actual work, SaveUserObject in this case. When finished processing the user selected objects, the listboxes are updated. That's all there is to the user interface piece.
Import / Export Code
As an example of the code to export an object from the current database to the repository, we will consider the function mentioned above, SaveUserObject().
Public Sub SaveUserObject(ByVal sType As String, sName As String) On Error GoTo Err_Handler Dim strTarget As String Dim lngType As Long strTarget = CurrentProject.Path & "\UserObjects.mdb" lngType = GetObjectType(sType) DoCmd.SetWarnings False DoCmd.CopyObject strTarget, sName, lngType, sName DoCmd.SetWarnings True Exit_Here: Exit Sub Err_Handler: MsgBox Err.Description, vbExclamation, "Error" Resume Exit_Here End Sub
Some things here require explanation. The object 'type' is passed as a string value like 'query' or 'form'. This needs to be converted into a long integer value that corresponds to the object: acTable = 0, acQuery = 1, etc. It would be nice if the Type values in MSysObjects corresponded to these constants, but they do not. So, I created a simple function, GetObjectType() that takes a string and returns the correct long integer value.
If the object doesn't exist in the repository, all is well, but where we are resaving or updating a repository object, a warning will present itself to the users, informing them that they are about to overwrite an object. While this may actually be a good warning to display, I have decided to suppress it. Turning warnings off by executing a DoCmd.SetWarnings False will prevent the user from being prompted. In your application, you may decide to comment out these lines and force the user to confirm the overwrite.
To Import an object, we use the DoCmd.TransferDatabase call in a similar code block.
DoCmd.TransferDatabase acImport, "microsoft access", strSource, lngType, sName, sName
Truth be told, it's been so long since I wrote this code that I forget why I use CopyObject() to do an Export and TransferDatabase() to do the Import, but there must be a good reason. You can play with it if you wish, but this is the code I've been using for years and it's tried and tested.
And Beyond ...
As you can see from the code above, the Import/Export piece is almost anticlimactic. The majority of the work is creating the queries that find the correct objects. There's also a snippet of code (not shown) that refreshes the table link from your database to the MSysObjects table in UserObjects.mdb. It's assumed this repository file will ALWAYS be in the same folder as the database that links to it, but that shouldn't impose a hardship on anyone.
So, this code works to allow users to manage objects one at a time. No sooner did I implement this at one client than I was asked to automate the process. Why not automatically copy out ALL new objects to a repository when the app closes and then import ALL missing objects back when the app opens? (We were pushing out a new client application file nightly, which caused no little consternation to users.)
The demo code includes two functions, SaveUserObjects_ALL() and RetrieveUserObjects_ALL(), which recursively call the SaveUserObject() and RetrieveUserObject() functions for each object in the corresponding queries. Unless there are a lot of objects, this script runs quickly and so far, users haven't complained ... at least not as much as they used to when their queries and reports were lost.