Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS Access

Posted Aug 19, 2005

Use System Tables to Manage Objects - Page 2

By Danny Lesandrini

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.

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



MS Access Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date