Focus on Popular Objects to Speed up an Access 2007 Database
July 18, 2008
A number of times this year I have been asked to speed up an Access database. This article outlines how usage data can make this quest more focused. The article also delves into an Access 2007 ACCDB only feature called TempVars. But firstly let's discuss some users scenarios that I've had to deal with this year.
Situation one: A skilled Excel technician has set up a database with numerous related tables. When he rang me for advice, he was adamant that he needed to upgrade to SQL server to speed up the database. As the database was only 20 MB in size, I doubted this but still we had the meeting. Some of the forms were very slow, had many Tab controls with hundreds of fields scattered across many subforms. In addition, the training users had was to scroll through records one at a time to find the record that they were interested in looking at. Just adding a find record box made finding the data a lot quicker.
Situation two: A complex database that had been in development for six years and now the developer had left the business. The last three years they had done nothing to the database apart from compacting and repairing the database but the performance was woeful. In this database, some forms could take up to two minutes to close when the close button was pressed. Also, some reports took nearly an hour to run.
Situation three: A huge database already converted to use SQL Server as a back-end; there were 250 forms and 80 tables. Performance was sluggish throughout during peak periods.
Okay. So what can you do? Initially I think it is safe to assume that the slow database will be a complex one. Therefore, if you want to speed up every object, it's going to take a long time. This means you can only work on some of the database and the best way to identify what objects in the database are worth working on is too initially proceed as follows.
Interview the key users to find out which forms and reports are slow and make a list. Find the five most popular slow objects from that list and work on those. This will give you a good idea how much difference you can make and how long it will take.
So lets have a look at how we can find out how often a form or a report is being used and then you can focus on how to make those popular objects go faster.
Tip: Remember that the database might need to be redesigned so don't plough in and change lots of finite detail just to speed up a database that really should be redesigned.
Logging When a Form or Report is used in Access 2007
To achieve this, I have rewritten my older VBA only logging code (that I wrote for my book on Access 2003 security) into a simpler solution that was inspired by Access 2007 features. To give myself a good Access 2007 database to test with, I downloaded the Tasks template from the suite of Access templates (see Figure 1) and added my new age logging solution to that.
Must Do: If you download a Microsoft Task database template or my download sample, save the ACCDB file into a Trusted Folder or you will be forever encumbered with the Disabled Content message bar.
When you open the Task template database, have a play around with the application until you get a feel for how the database works. I found that the new Task template was very well designed and had some neat Outlook 2007 integration as well. Anyway, once you have done that, click on the pull down arrow on the navigation bar (shown in Figure 2) and choose All Access Objects and Object Types as shown in Figure 3. This is the best view for the database changes that I am about to show you.
Making the Download Database Code Work for You
To make the sample code work in your database, you will need to import the four objects shown in Figure 4 into your database. The table is where the data is stored, the query is what adds the log record, the macro is what you run from your report or form and the module has the code to grab the information for the log.
Adding the Log Macro to Your Forms
Here is all you have to do to add object logging to your database. Open your Form in design mode as I have done in Figure 5. Turn on the Property Sheet on the design Ribbon and choose the On Close event. Select mcrLogUsage.LogForm from the list. Note that I have used the Close event as this works better with my logging code than the On Open event. Thats all the steps required, the next time that your users Open (and subsequently close) that Form, a record is added to the log.
When you have followed those setup instructions, you will end up with a log entry (shown in Figure 6) each time anyone opens the form.
Adding the Log Macro to Your Reports
To create a log entry for a report, do exactly the same as for a Form. Open the report in design view; show the Properties Sheet, find the Close Event and this time choose mcrLogUsage.LogReport.
Finding out How Many Times An Object Has Been Used
To work out how many times an object has been used, set up a query with this SQL syntax.
SELECT ObjectName, ObjectType, Count(OpenTime) AS NoTimes FROM UserObjectLogs GROUP BY ObjectName, ObjectType;
If you run this, you will end up with results like that shown in Figure 7.
This query can be found in the download database and is called qryUsageCount.
Software Installation Conclusion
Thats all you need to do to workout which are your most popular forms and reports. Then if you have to speed up a number of forms and reports, concentrate on the ones are being used the most. Remember that sub-forms and sub-reports will not be included in this log.
The Technical (and Interesting) Details
Many of the improvements to Access 2007 were targeted at making great downloadable templates (like those shown in Figure 1). As a result, there have been many innovations in Macros to create database templates that could be shipped VBA code free. One of the most important changes that made that happen is a new object called a TempVar. Lets look at how I used TempVars in this database.
In the Module logObjects_FXL12, you will find code that will extract the last form or report that had focus and find out the users Windows account. These answers are then allocated to 3 different TempVars. Then a query is opened that uses those tempVars to insert a new record in our users log table.
Option Compare Database Option Explicit Public Function LogFormUsage() On Error Resume Next TempVars.Add "ObjectName", Screen.ActiveForm.Name TempVars.Add "ObjectType", "3" Call LogUsage Exit Function End Function Public Function LogReportUsage() On Error Resume Next TempVars.Add "ObjectName", Screen.ActiveReport.Name TempVars.Add "ObjectType", "4" Call LogUsage Exit Function End Function Public Sub LogUsage() On Error Resume Next TempVars.Add "WindowsAccount", User_FX DoCmd.SetWarnings False DoCmd.OpenQuery "qryUpdateLogs" DoCmd.SetWarnings True Exit Sub End Sub
Note: If you look carefully at the code, you will see a function called User_FX, which retrieves the Windows User account. The code for this is in the download database.
TempVars Versus Global VBA Variables
TempVars are handy for moving values between queries and forms and form controls and macros and VBA. Before 2007, you could only do this with global variables and even then, that only applied to VBA code. The problem with global variables was that if the VBA code failed, you could lose the information in the global variable. TempVars are far more robust.
The Query That Uses TempVars
In the database you will find an append query called qryUpdateLogs. This gets its values from the TempVars as illustrated in Figure 8. In my opinion, TempVars really are a simple way of getting complex stuff into a query without using docmd.RunSQL or CurrentDB.Execute.
The Macros and The End
Finally, if you look at Figure 9, you will see the two macros that I have setup to add to your form and report Close events. You will see that the macros are stored in a macro container and you will also notice that there are two lines of macro logic at the top to stop anyone running the macro by itself because the code would fail in this situation.
So thats the end of the story on logging usage of objects in databases. I hope as you read this article, you picked up some good tips about using TempVars and how to code in Access 2007 databases without exclusively relying on VBA. I also hope that you can speed up your database.
In my book on Access Protection And Security, I go into quite a lot of detail on how you can add object logging to your VBA code in Access 2000/2003 MDB databases. You can still purchase this book from http://www.vb123.com/map
If you want the low-down on database templates and are a committed Access developer, try reading The Rational Guide to Microsoft Office Access 2007 Templates by Zac Woodall from the Microsoft Access team. I like this book because it gives some interesting low downs on why Access 2007 headed in the direction that it did.
» See All Articles by Columnist Garry Robinson