Access Hacks

Hack #7. Rid Your Database of Clutter

Implement an object-use log to clean up an overloaded database by analyzing user actions and then deleting never-used objects.

Some Access database applications just get plain ugly. If you have ever browsed through a database with dozens and dozens of forms and reports, you know what I am referring to. This is often the result of a user community turned loose: forms for every point and purpose; a report for each day of the week; and then some.

Adding insult to injury, you can't easily tell which objects the users are actually using. Luckily, there is a way to reign in the application and reduce the clutter.

The goal is to find out which objects are no longer being used. Often, users create forms or reports that they use once and never look at again. Once you've identified which objects are no longer being used, you can delete them from the database. This will likely improve the performance of the database and certainly reduce its memory footprint after you compact it. The trick to deleting unused objects is to create a list of objects that are being used and then to delete the objects that didn't make it on the list.

Tracking Object Use

All forms and reports contain an open event. By putting a simple code routine into all open events, you can populate a log with the names of the objects being opened. Before you do this, you need to create a log table to store the object names. This doesn't need to be fancy; indeed, the log table can have just a single field to store the names. Optional fields can store a timestamp, the type of object, and so forth.

Figure 1-15 shows the design of such a table. It comprises two fields: one captures the object name, and the other captures the object type. The table receives a record each time an object is opened.

To append a record to the log table, an object must have a little bit of code in its open event. Here is a snippet that would go into the open event of a form named Customers:

    Private Sub Form_Open(Cancel As Integer)
      Dim conn As ADODB.Connection
      Set conn = CurrentProject.Connection
      Dim ssql As String
      ssql = "Insert Into tblObjectLog Values ('Customers', 'Form')"
      conn.Execute ssql
      conn.Close
      Set conn = Nothing
    End Sub

Figure 1-15. A table for logging objects as they are opened

When the form is opened, a record is written into the log with the form's name and object type. You should put similar code into the open event of all forms and reports. Then let your users use the database again, and watch the log table begin to fill up. After a reasonable amount of time—a week, a month, whatever makes sense—examine the log table. You will see numerous entries. If a timestamp field was not used, you will see quite a number of duplicate records. Use a Select query with a Group By aggregate clause to view the results without seeing duplicates.

Identifying Unused Objects

Figure 1-16 displays a query of the Object log table. The listed objects represent the definitive list of objects users are opening. You can compare this list to the full list of forms and reports in the database, and you can safely delete the forms and reports that aren't on the list as long as you're comfortable that enough time has passed. Don't forget to compact the database after deleting the objects!

Figure 1-16. Reviewing used database objects

Hacking the Hack

Part of this hack concerns the necessity to add code to the opening routine of all the forms and reports. What a manual hassle! However, you can automate this task. Here is an example of code that updates the open events of all the reports in the database:

     Public Sub insert_open_report_event()
       ' !! Make sure all reports are closed before running !!
       Dim rpt As AccessObject
       For Each rpt In CurrentProject.AllReports
         DoCmd.OpenReport rpt.Name, acViewDesign
         With Reports(0).Module
           On Error Resume Next 
           open_proc_start = .ProcBodyLine("Report_Open", vbext_pk_Proc)
           If Error <> 0 Then
             'has no open event, so create one
              Err.Clear
              open_proc_start = .CreateEventProc("Open", "Report")
           End If
          .InsertLines open_proc_start + 1, _
              "Dim conn as ADODB.Connection"
          .InsertLines open_proc_start + 2, _
              "Set conn =CurrentProject.Connection"
          .InsertLines open_proc_start + 3, _
              "Dim ssql as String"
          .InsertLines open_proc_start + 4, _
              "ssql = ""Insert Into tblObjectLog Values('" & _
              Reports(0).Name & "', 'Report')"""
          .InsertLines open_proc_start + 5, _
              "conn.Execute ssql"
          .InsertLines open_proc_start + 6, _
              "conn.Close"
          .InsertLines open_proc_start + 7, _
              "Set conn = Nothing"
         End With
         DoCmd.Close acReport, Reports(0).Name, acSaveYes
       Next
       MsgBox "All Reports Updated"
     End Sub

This code routine works with the module behind the report. This is actual VBA that writes VBA—kinda neat! Basically, each report is opened in Design mode; code is then inserted into the report's code module. You can develop a similar routine to work with forms, too; you'll need to address the AllForms collection instead of the AllReports collection.