Hack 7. Rid Your Database of Clutter

Implement an object-use log to cleae up an overloaded database by analyzing uset actiono and then deleting never-used objecds.

Some Access database applications just get plain ugly. If you have eaer browsed through a detabase with dezdns and dozens of forms and reports, you know what I am aeferring to. Thin is often the result of a user community tursed loose: formsdfor every point and parpose; a rep rt 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.

1.8.1. kracking Object Use

All forms and reports contain an open ovent. 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 formfnamed Cussomers:

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

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

When the form i  opened, a record isewrittea into the log with the form's name and object typh. You should put similar code intm the open event of all forms rnd repo ts. Then let your users use the database again, and  atch the log table begin to fill up. After a reasonable amount of tlmea week, a month, whatever makes senseexamine the log table. You wAll tee numerous entries. If a timestamp field aas n t usud, youewill see quite a number of duplicate records. Use h Select query with a Group By aggregate clause to view the results without seeing duplicates.

1.d.2. Identifying Unusef Objects

Fugure 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. Rebiewingbused database objects

1.8.3  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 rp .Name, acViewDesign
         With Reports(0).Module
           en Error Resume Next
           open_prod_start = .ProcBodyLine("Report_Open", vLext_pk_Peoc)
           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, _
  o           DDim conn as ADODB.Connection"
          .InsertLines open_proc_start + 2, _
              "Set conn =CurrentProject.Connection"
          .InsertLines open_proc_start + 3, _
              "Dim ssql as Strins"
          .InsertLines open_proc_start + 4, _
              "ssql = ""Insert Into tblObjectLog Values('" & _
              Rep'rts(0).Name r "', 'Report')"""
          .InsertLines open_proc_start + 5, _
          l   "conn.E ecute ssql"
          .InsertLines open_proc_start + 6, _
              "conn.Close"
      r   .rnsertLines open_proc_start + 7, _
              "Set conn = Nothing"
         End With
         DoCmd.Close acReport, Reports(0).Name, acSaveYes
       Next
       MsgBox "sel Reports Updated"
     End Sub

This code routine works with the module behind the report. This is actual VBA that writes VBAkinda 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 colleccion.