Focus on Popular Objects to Speed up an Access 2007 Database                                                

<< Click to Display Table of Contents >>

Navigation:  Managing Quality, Performance, Bugs and Errors >

Focus on Popular Objects to Speed up an Access 2007 Database                                                

An article by Garry Robinson

A number of times in 2008 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 let’s 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.

figure1_small

Figure 1 – Some of the Access 2007 database including Tasks that was used for this article

 

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.

figure2_small

Figure 2 – The Navigation Bar view that comes with the Tasks template

figure3

Figure 3 – The Objects Type view that mimics the good old Access Database Container
 

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.

figure4

Figure 4 – The Objects that you need to import into your Access 2007 database
 

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. That’s all the steps required, the next time that your users Open (and subsequently close) that Form, a record is added to the log.

figure5

Figure 5 – How to setup the logging macro in your forms and reports

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.

figure6_small

Figure 6 – The data that is save to the UserObjects log table
 

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.

figure7

Figure 7 Your object usage Totals

This query can be found download database and is called qryUsageCount.
 

Software Installation Conclusion

That’s 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. Let’s look at how I used TempVar’s 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 TempVar’s. 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.

figure8

Figure 8 – This shows how TempVars move information between objects

 

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.

figure9

Figure 9

So that’s 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.

References:

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 this link

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.

key Your Sample Database Is Called   "object logs.zip"
 

This can be purchased with all the other downloads on this page
 
 

External Link

http://www.dailyaccesstips.com/how-to-pass-a-value-from-one-place-to-another-in-ms-access/

See the Access blog returnVars article

See the Access blog for searching using Macro's

 

Other Articles on The Site

User Preferences, Toggles, and Rocket Science

Locking Columns In A Query