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