Improving Application Performance

Users quickly grow tired of waiting for a slow application to respond. Performance is particularly a problem in a network environment, where multiple users can be running an application and requesting its data simultaneously, and your application data has to share network resources with all of an organization’s other applications. In this section, we’ll examine some steps that you can take to optimize the performance of your multiuser Access application.

Separating Data from Other Access Objects

Obviously, the major reason for making a centralized database available over the network for users to share is that the data it contains is dynamic to some degree. If data were static, once the data was assembled, it could simply be distributed to each user and run on his or her desktop. A network to share data would be unnecessary, and issues of performance and network traffic need never arise.

Generally, though, if you look at the components of your Access application, you’ll find that most of them are in fact static. Except for periodic maintenance and upgrades, objects such as forms, reports, queries, and macros tend to change little if at all. The data stored in tables is the dynamic part of an Access application. Yet, if all database objects are stored in one centralized database, each time users access the application, they receive these same static objects along with dynamic data. Sending the same static data, like forms and code, over the network again and again is a real drag on performance. Instead, it is much more efficient to use the network only to send the dynamic data of a database application, and to load the static form, report, and query objects from the user’s local system.

To address this issue, Access (from Access 97 on) offers a utility that allows you to split your database into a front end that resides on the user’s desktop and a back end that resides on a single machine and is shared by all users of the application. Called the Database Splitter, this utility can be accessed by selecting Tools | Database Utilities | Database Splitter.

Before using the splitter and dividing your database application into components, it’s best to make a backup copy of your original database. To do this, select File | Back Up Database. Access closes the open database file and opens the Save Backup As dialog box, a standard File Save dialog box. As you can see in Figure 20-2, Access suggests a filename that consists of your original database filename along with the date of the backup. Feel free to assign a filename and a directory of your choice. Then select the Save button to begin the backup. When the backup is completed, Access will reopen the original database.

Figure 20-2: Save Backup As dialog box

When you launch the Database Splitter Wizard, Access displays the dialog box shown in Figure 20-3, which allows you to cancel the operation if you’ve forgotten to back up your database or if you’ve launched the wizard in error. If you select the Split Database button, Access prompts you to select a name and location for the back-end part of the database, as Figure 20-4 shows. This consists of the Access objects that you will store on a single system (your database server) and that typically are fairly dynamic, such as your nonstatic database tables. In forming a recommended filename, Access simply adds the substring “_be” to your existing filename. You can change it or accept the default. Click the Split button when you’re satisfied with the name and location of your back-end database component.

Figure 20-3: First screen of the Database Splitter Wizard

Figure 20-4: The Database Splitter Wizard’s Create Back-end Database dialog box

Eventually, depending on the number of objects in your database and its size, Access should display a dialog box informing you that the database has been successfully split. This means that your Access database (.mdb) file has been separated into two distinct files: all tables have been saved in the back-end database file, while all queries, forms, reports, macros, and modules have been saved in the front-end file. This front-end file is a modified version of your original database file (in our case, thecornerbookstore.mdb), except that its tables have been removed. This is indicated by the icon beside each table in the front-end database’s Tables window, shown in Figure 20-5, which shows that links to the tables have replaced the tables themselves.

Figure 20-5: The Tables window of a front-end database

Once you’ve split the database into a front and a back end, you can copy the front-end file to individual users’ machines, as well as move the back-end database file to another location. Of course, moving either or both of these database files usually breaks the front-end database’s links to the back-end tables. For your application to work, you must update these links to reflect the current location of the back-end database. Otherwise, the front end of your application will simply be unable to locate the back end, and you’ll see an error dialog box like this one:

To correct this problem, Access provides the Linked Table Manager, which you can activate by selecting Tools | Linked Table Manager, or by right-clicking on one of the front-end linked tables and selecting Linked Table Manager from the pop-up menu. Access then opens the Linked Table Manager dialog box, shown in Figure 20-6. The dialog box lists the linked tables in the current database and shows the location of the physical tables to which they point. You can select the links that you’d like to update, then click the OK button. Access will then display a File Open dialog box that prompts you for the new location of the back-end database file. If Access finds all of the physical tables in the file you’ve selected, it will update the remaining links as well. Otherwise, it will prompt you for each remaining table whose link it can’t resolve.

Figure 20-6: Linked Table Manager dialog box

When it finishes, Access will display a dialog box informing you that the links have been successfully updated. Wherever possible, you should make sure that the links are correct before you distribute the client component of your database application to individual users.

In most cases, the default behavior of separating tables in a back-end database file from everything else in a front-end database file is acceptable. But if one or more of your tables contain static data (like a state or a zip code table), you might want to include it in the front-end database so that its data doesn’t contribute to network traffic. You can do this by opening the front-end database file and performing the following steps:

1. Select the linked table in the Tables window, and then select Edit | Copy.

2. Select Edit | Paste. Access displays the Paste Table As dialog box shown in Figure 20-7.

3. In the dialog box, assign a unique name to the table (it can’t be the same as the linked table), select the Structure and Data (Local Table) option, and then click OK.

   
   Figure 20-7: The Paste Table As dialog box

You can then delete the original linked table and rename the new table. In addition, you can open the back-end database file and delete the file that you’ve just added to your front-end database file.

Although splitting a database can significantly boost an application’s performance, it also has one major drawback: each time you make changes to the static database objects, like forms, reports, and queries, you have to redistribute the front-end component to your users. Depending on how often your application changes and how many users there are, this can be a real nuisance. Because of this, it makes sense to split your database only when its supposedly static portions have become more or less stable. Alternatively, you may want to include more “dynamic” static objects in the back-end of your database application, so that they continue to be sent across the network, while more “static” objects continue to reside in the front-end component.

Centralization vs. Decentralization

Whether you should split your database is only part of a larger question of what the overall architecture of your application should be and how centralized it should be. In addition to deciding how your database application is to be distributed, if it is to be distributed at all, you should decide how Access is to be distributed.

At one extreme, it is possible for Access to reside on a file server, with each user downloading not only an Access application but also Access itself and all of its associated library files. This allows for complete centralization of Access, reduces the cost of Access licenses, minimizes hard disk storage requirements, and allows for the use of diskless workstations. But it also increases the load on the single file server and results in an enormous increase in network traffic. In many cases, this will be a load that the network cannot bear and that Access, especially since it was engineered as a single-desktop product first and a multiuser product second, cannot efficiently support.

At the other extreme is a decentralized solution, where Access (and possibly the front end of Access applications) resides on each desktop, and a single file server is responsible for providing the data for front-end requests. This, of course, places a copy in the hand of each user, which maximizes licensing fees, consumes more disk space, and has greater hardware costs. It also offers the best performance.

In between the two extremes are a number of possible solutions. It is possible to install Access on a single file server, for instance, but to install many of its libraries on individual users’ machines. By splitting up the elements of your architecture and locating some on the client and some on the server, you may be able to optimize a number of goals (performance, administrative control, cost, and efficiency) at once.

Compiling Your Code

At runtime, VBA code executes in a compiled environment. This means that before it is run, the source code that you write is translated into executable code by a VBA language compiler. This differs from an interpreted environment, where source code is translated into executable code as the source code is encountered.

Access compiles its VBA code dynamically before running it. This means that if your code calls a function that has not been compiled, the module that contains it will be compiled automatically before it is executed. As a result, you don’t have to make sure that all of your code is compiled; Access will handle the compilation process for you. The downside, though, is that compilation can be time consuming. The first time that the code in a module is run, the application’s performance can be noticeably slower because of the compilation process. (Once the code is compiled, though, performance should improve.)

In addition to its effect on performance, uncompiled code has another undesirable side effect: it is not necessarily syntactically correct, and may not compile at all. For instance, if you’ve made a modification to a code module but have never subsequently tested the code, it may not have been compiled. And if your modification itself introduced an error, that error may be discovered at runtime by the user, when Access is unable to compile the module’s code.

You can eliminate this performance bottleneck and insure that your code will compile by compiling the code yourself. Access in fact provides two methods to do this. The first and simplest method involves selecting the Debug | Compile <project_name> menu option, which causes Access to compile all of a project’s VBA code. At a minimum, it’s a good idea to compile all VBA code when you’ve finished coding and are ready to place your code in production.

The second option is to create and distribute a compiled .mde file, instead of the conventional Access .mdb file. This not only compiles your code, but also removes all source code, thus hiding it from prying eyes and making it impossible for the users of your application to see or to modify your source code. It also compacts the destination database, which makes it possible to compile the front end of a split database (discussed in the section “Separating Data from Other Access Objects” earlier in this chapter).

An .mde file also disables the following features that are commonly available to users from the Access interface:

• The ability to create or modify forms, reports, or modules. (Note that the user can continue to create new tables and queries and to modify existing tables and queries.)

• The ability to insert modules or class modules in the VBA Editor.

• The ability to add, delete, or change references to libraries in the VBA Editor.

• The ability to view existing modules or “code behind forms” in the VBA Editor.

To create an .mde file, select Tools | Database Utilities | Make MDE File. When the Save MDE As dialog box appears, enter the name of the .mde file (by default, it will be given the same name as the .mdb file from which it is created) and select the directory where you’d like the file to be saved. When you select the Save button, Access will close the open .mdb file, create the .mde file, and then reopen the .mdb file.

If the .mde file becomes the production version of your application, be sure to keep the .mdb file in a safe place. Since the .mde file no longer includes your source code in text form, you can make no further modifications to the application. Instead, you’ll have to make modifications to the latest version of the .mdb file and recompile it into an .mde file.

Writing Optimized VBA Code

Because multiuser applications tend to perform more slowly than single-user ones, it is especially important in a multiuser application to write optimized code. Performance that is acceptable or barely acceptable in a single-user application easily becomes unacceptable in a networked environment. In this section, we’ll examine three of the major techniques that you can use to improve your code’s performance. Although all three techniques apply equally to both single-user and multiuser environments, they can have the greatest impact on performance in a multiuser or networked environment.

One of the greatest performance gains comes from taking advantage of early binding. Binding refers to the point at which the identity of an object becomes known. Early binding means that the identity of an object is established when the code is compiled and before it is actually run, so that all information about the object (such as its methods and method parameters) can be verified. Late binding means that the identity of an object becomes known only as code is run.

In VBA, you use late binding by using the generic Object data type. To get a sense of what this means, the following code, which originally appeared in Chapter 12, opens an Access form using early binding:

Sub runForm()   Dim con As ADODB.Connection  ' con is early bound   Set con = New ADODB.Connection   con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _   "Data Source=C:\BegVBA\thecornerbookstore.mdb;"   DoCmd.OpenForm "frmCustomer" End Sub

The following is a late-bound version of the same code:

Sub runLate()   Dim con As Object ' con is late-bound     Set con = New ADODB.Connection   con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _   "Data Source=C:\BegVBA\thecornerbookstore.mdb;"     DoCmd.OpenForm "frmCustomer" End Sub

Here, the precise object type of the con variable can only be resolved when VBA encounters the Set keyword, rather than when con is declared by the Dim statement. The result is that, on my system, the second version of the code took twice as long to run as the first. The conclusion is clear: use early binding wherever possible.

The final two performance tips that we’ll look at focus on code executed in loops. Since loops execute multiple times, they tend to consume a relatively large proportion of total execution time. If the code within a loop is poorly written, it can severely degrade overall performance.

One technique for minimizing the time that code within a loop executes is to exit the loop as soon as possible using the Exit Do (for Do loops) or Exit For (for For and For Each loops) statements. Although it may seem obvious, the fact that a loop can be exited “prematurely” is frequently forgotten when coding.

For instance, if you are working with a large recordset and looping through each of its records, you can save a significant amount of time by exiting the loop once you have finished, rather than iterating each of the records in which you are no longer interested. Similarly, if you are using a loop to examine a number of controls for a particular property value and you find that value, you can immediately exit the loop without examining any additional controls. The exact improvement in performance attributable to prematurely exiting a loop varies depending on the number of iterations of the loop that are eliminated.

Second, every operation within a loop necessarily occurs every time the loop is executed. Sometimes, though, those operations actually need to be performed only once and are not dependent on the loop. Variable declaration is a case in point. Take the following code fragment, for example:

Public Sub Loops()   Dim con As ADODB.Connection   Dim rs As New ADODB.Recordset   Dim strList As String     Set con = New ADODB.Connection     con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _   "Data Source=C:\BegVBA\thecornerbookstore.mdb;"     rs.CursorLocation = adUseServer   rs.Open "SELECT * FROM tblCustomer", con, adOpenStatic, adLockOptimistic     Do While Not rs.EOF   ' Variable should be declared outside of loop   Dim strName As String   strName = rs!txtCustFirstName & " " & rs!txtCustLastName   strList = strList & strName & vbCrLf   rs.MoveNext   Loop   MsgBox rs.RecordCount & " records: " & vbCrLf & strList     Set rs = Nothing   Set con = Nothing  End Sub

The strName string variable is declared inside the loop, which means that it is declared anew each time the loop executes. If you move the variable declaration outside of the loop, this code executes approximately 25 percent faster than if the variable is declared inside the loop.