Hack 7. Rid Your Database of Clutter
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.
1.8.1. 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 timea week, a month, whatever makes senseexamine 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.
1.8.2. 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
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 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 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 collection.