Beginning Access 2007 VBA

As part of the testing process, you should fine-tune the application to make it run faster and better. You can apply various techniques to improve the real speed, as well as the speed as perceived by users.

Improving Actual Performance

The speed at which your application actually performs can be improved by applying some general design guidelines, coding techniques, and data access techniques. You will now look at each of these in turn.

General Design Guidelines

Here are several general guidelines to help improve the actual performance of your applications:

• Minimize the code and objects in a form. Move the code to a standard module. The more complicated a form is, the longer it takes to load.

• Minimize the use of subforms because two forms are in memory.

• Write reusable modules that so you are not rewriting the same code in slightly different ways multiple times. This reduces the amount of code in your application and helps improve speed. For example, instead of having one procedure that enables all controls in one form, another that disables all controls in another form, and two others that disable controls in each of those same forms, write a generic module that enables or disables all controls on the specified form based on whether the enable or disable flag is passed as a variable.

• Eliminate code that is not used.

• Make sure that your application is compiled. Also, consider compiling your application into an ACCDE file as described later in this chapter.

• If you have all the user-interface objects in one database and the data tables in another (Access or other) database, open the database with the user interface exclusively for faster performance. On the File Open dialog box, you can specify the Open Exclusive setting to open the database exclusively, as shown in Figure 12-7.

  Figure 12-7

Let’s now look at some ways you can improve performance by writing better VBA code.

Optimizing VBA Code

You have various ways to accomplish the same task using VBA code, some of which are faster than others. Here are some examples:

• Use With...End With when dealing with multiple settings for the same object. Otherwise, the process may be slow because the object has to be located each time. It is generally better to use:

  With txtName .Text = "Hello" .Visible = True .Enabled = True End With

  than the following:

  txtName.Text = "Hello" txtName.Visible = True txtName.Enabled = True

• Use the correct data type and make it only as large as you really need. For example, if you need an integer, do not use a double. Minimize the use of variants because they are the slowest. They must take time to determine a data type because one was not explicitly designated.

• When you are working with automation, as I described in Chapter 11, use early binding so references are resolved at compile time instead of runtime. For example, early binding to Excel like this:

  Dim objExcel As Excel.Application Set objExcel = New Excel.Application

  is better than late binding to Excel like this:

  'declare a new generic object Dim objExcel as Object 'instantiate the new object as an Excel object Set objExcel = CreateObject("Excel.Application")

• If...Then...Else is faster than IIF() statements.

• Instead of testing a Boolean variable for True, use the value explicitly. For example, use:

  If blnValue Then

  instead of:

  If blnValue = True Then

• Use SQL queries to do batch inserts, updates, and deletes instead of looping through recordsets.

Improving Data Access

The performance of Access applications can be degraded because of improper data access. To make your applications run faster, keep the following guidelines in mind:

• Minimize the number of database connections open. For example, if you use a bound form, it maintains a constant connection to the database. Consider switching to an unbound form that connects to the database to retrieve the data, disconnects, and reconnects when it needs to update the data. See Chapter 5 on ADO for more information.

• Use a query as the source of a form, control, or ADO recordset that returns only the rows you need to work with instead of opening an entire table.

• Make sure that your query is written correctly. For example, make sure to retrieve only the columns that are being used. Also make sure that you are using the correct type of join.

• Make sure the type of cursor being used is not more than you really need. For example, if you are not modifying the data, use a read-only cursor.

• Use proper indexes on the tables to reduce the number of table scans. Add indexes to the fields that are used to join tables together and are frequently used to retrieve data. This will save Access from having to perform a scan of the entire database to find the desired record. However, do not add indexes on every field in the database. This will actually degrade performance more than having no indexes at all.

Improving Perceived Performance

The prior sections focused on how to improve the actual performance of your application. In this section, you look at a few ways to improve the performance as perceived by the end users. In other words, you are not really speeding up the application by implementing these features, but you make the user feel like the application is running faster or that progress is being made.

• Use an hourglass to indicate that the application is performing a task that may take a while. The hourglass can be turned on and off, respectively, with the following code:

  DoCmd.Hourglass True DoCmd.Hourglass False

• Hide forms that need to be reopened later. Instead of constantly opening and closing the same form, consider opening the form once and hiding it until it is needed again. Forms that remain in memory are faster than forms that are reloaded frequently, although they both take up resources. You use the Visible property of the form to hide and redisplay the form. For example, the following code hides a form:

  frmName.Visible = False

• Use a splash screen when the application loads to indicate that something is happening. The splash screen should be a very simple screen with as few controls as possible. The idea is to get something in front of the user quickly while the rest of the application loads. To create a splash screen, just create a simple form and have that form load when the application begins. You then close the splash screen from code when you are ready to display the main form.

• Use a progress bar or percent meter to indicate the progress of an operation. For example, if you are performing an operation that is going to take some time iterating through multiple steps, you want to inform the user as the operation progresses. Think of a file copy operation. As the files are copied, you see the progress bar to see how many files remain. You can either create your own form to display the progress of an operation, or you can use the percent meter built into Access. The following is an example of using the percent meter that displays progress in the bottom-left corner of Access:

  Dim intCounter As Integer Dim intResult As Integer Dim intPause As Long intResult = SysCmd(acSysCmdInitMeter, "Copying 2000 files", 2000) For intCounter = 1 To 2000 intResult = SysCmd(acSysCmdUpdateMeter, intCounter) 'Create pause so user can see progress bar For intPause = 1 To 20000 Next intPause Next intCounter intResult = SysCmd(acSysCmdRemoveMeter)

Running the Performance Analyzer

Access comes with a Performance Analyzer utility that will analyze the selected objects and make suggestions to help improve the speed. You must have the database you wish to analyze open in order to run the analyzer.

Try It Out-Using the Performance Analyzer

Let’s walk through an example of using the Performance Analyzer to analyze all the objects in the Northwind database for suggested ways to improve speed.

1. Select Database Tools and then click on Analyze Performance in the Analyze ribbon. A screen similar to the one shown in Figure 12-8 is displayed.

   Figure 12-8

2. Select the All Object Types tab, as shown in Figure 12-8. Click the Select All button and then select OK. This selects all the objects in the database to analyze. The analyzer runs, and a screen similar to the one shown in Figure 12-9 is displayed, which makes various suggestions on ways to improve the performance of the application.

   Figure 12-9

How It Works

The Performance Analyzer is a wizard you can use to let Access provide some suggestions on how to improve the performance of the application. In the example, you ran the wizard against the Northwind database and discovered that some additional improvements can still be made to make the application run even faster.