Chapter 18. Optimizing Your Application

IN THIS CHAPTER

• Why This Chapter Is Important
• Introducing Optimization
• Modifying Hardware and Software Configurations
• What Is the Access Database Engine?
• Letting the Performance Analyzer Determine Problem Areas
• Designing Tables to Optimize Performance
• Designing Database Objects to Improve Performance
• Practical Examples: Improving the Performance of Your Applications

Why This Chapter Is Important

In a world where it can be difficult for hardware to keep up with software, it is important to do everything you can to improve the performance of your application. This chapter helps you optimize your application’s speed and reduce its memory and hard disk space requirements.

Introducing Optimization

Optimization is the process of reviewing your operating environment, Visual Basic for Applications (VBA) code, application objects, and data structures to ensure that they are providing optimum performance. In a nutshell, optimization is the process of making your application leaner and meaner.

Users become frustrated when an application runs slowly. In fact, if a user is not warned about a slow process, he will often reboot or shut down the power on the machine while a process is running. This can have dire results on the integrity of the data.

You can take many steps to optimize an application’s performance, ranging from using a front-end tool such as the Performance Analyzer, to fastidiously adhering to certain coding techniques. This chapter highlights the major steps you can take to optimize the performance of your applications.

Modifying Hardware and Software Configurations

The Access environment refers to the combination of hardware and software configurations under which Microsoft Access runs. These environmental settings can greatly affect the performance of an Access application.

The easiest way to improve an application’s performance is to upgrade its hardware and software configuration. This form of optimization requires no direct intervention from the developer. Plus, a side benefit of most of the environmental changes you can make is that any improvements made to the environment are beneficial to users in all their Windows applications.

Improving the environment involves more than just adding some RAM. It also can mean optimally configuring the operating system and the Access application.

Hardware, Hardware, More Hardware, Please!

The bottom line is that Windows XP, Windows Server 2003, Windows Vista, and Access 2007 all crave hardware—the more, the better. The faster your users’ machines are, and the more memory they have, the better your applications will run. Obtaining additional hardware might not be the least expensive solution, but it certainly is the quickest and easiest thing you can do to improve the performance of your application. You can make a number of changes to your system’s hardware to improve your application’s performance, as the next sections illustrate.

RAM, RAM—That’s All I Need!

Memory is what Access craves most, whether you are running under the full version of Microsoft Access or using the runtime version of the product. Microsoft Access requires 256MB of RAM just to run, and Microsoft recommends additional RAM if possible. Microsoft considers these minimums to be the standard operating environment for Access. Microsoft recommends additional RAM for each application that your users are running simultaneously with Access 2007. Put in a straightforward way, the more RAM you and the users of your application have, the better. A great environment for Access 2007 is 2GB or more of RAM. In fact, if every one of your users has at least 2GB of RAM, you can stop reading this chapter, because everything else covered here is going to provide you with minor benefits compared to adding more RAM. If you are like most of us, though (meaning that not every one of your users has a machine running at 1GHz with 2GB of RAM or more), read on.

Defragment Your User’s Hard Disk

As your computer writes information to disk, it attempts to find contiguous space on which to place data files. As the hard disk fills up, the computer places files in fragmented pieces on the hard disk. Each time your application attempts to read data and programs, it must locate the information scattered over the disk. This is a time-consuming process. Therefore, it’s helpful to defragment the hard disk on which the application and data tables are stored using a utility such as the Disk Defragmenter that ships with Windows XP, Windows 2003, and Windows Vista.

Compact Your Database

Just as the operating system fragments your files over time, Access itself introduces its own form of fragmentation. Each time you add and modify data, your database grows. When you delete data or objects within your database, it does not shrink. Instead, Access leaves empty pages available in which it will place new data. The problem is that these empty pages are not necessarily filled with data. You can free the empty space using the Compact utility, which is included in the Microsoft Access software. The Compact utility frees this excess space and attempts to make all data pages contiguous. You should compact your database frequently, especially if records or database objects (for example, forms and reports) are regularly added and deleted. You can access the Compact utility by clicking the Microsoft Access button and selecting Manage, Compact and Repair Database.

Tune Virtual Memory: Tweak the Paging File

Although Windows XP, Windows Server 2003, and Windows Vista attempt to manage virtual memory on their own, you might find it useful to provide them with some additional advice. To modify the physical location of the paging file, right-click My Computer, and choose Properties. The System Properties dialog box appears. Click the Advanced tab (see Figure 18.1). Then click Settings under the Performance options. The Performance Options dialog box appears. Click the Advanced tab (see Figure 18.2). Click the Change button under Virtual Memory. The Virtual Memory dialog box appears (see Figure 18.3). In this dialog box, you can modify all the settings for the paging file. It might be useful to move the paging file to a faster disk drive or to a drive connected to a separate controller card. Any changes you make might adversely affect performance. It is important that you evaluate whether any changes you make will help the situation—or perhaps make things worse! In general, it is advisable to let Windows dynamically manage the size of the paging file unless the system is running very low on disk space.

Figure 18.1. The Advanced tab of the System Properties dialog box.

Figure 18.2. The Advanced tab of the Performance Options dialog box.

Figure 18.3. The Virtual Memory dialog box allows you to change paging file settings.

Run Access and Your Application Locally

In Chapter 22, “Developing Multiuser and Enterprise Applications,” you will learn that it is best to install both the Access software and your application objects on each user’s local machine. You should store only the data tables on a network file server. Otherwise, you will be sending dynamic link libraries (DLLs), object linking and embedding (OLE) objects, help files, type libraries, executables, and database objects all over the network wire.

Do Everything You Can to Make Windows Itself Faster

I am always amused that the users with the slowest machines and the least memory have the most accessories running. These accessories include multimedia, fancy wallpapers, and other nifty utilities. If performance is a problem, you might try to see whether eliminating some of the frivolous niceties improves the performance of your application. If it does, encourage the user to eliminate the frills, get more memory, or accept your application’s performance. Furthermore, if you are finished using other applications, such as Microsoft Excel, close them. This frees up system memory for Access.

If you have Windows XP, a tip to make it run faster is to shut down and restart on a regular basis. Memory tends to become fragmented, making applications run more slowly. Although I can go weeks or months in Windows 2003 Server without rebooting, I find it beneficial to reboot my Windows XP machine once a week.

Change Access’s Software Settings

In addition to the more obvious measures just outlined, some minor software tweaking can go a long way toward improving performance. Adjusting several settings in the Windows Registry can dramatically improve performance. All these changes involve the Registry’s ISAM section. The properties you might want to change include MaxBufferSize and ReadAheadPages. Both of these settings determine how the Jet Engine uses memory.

MaxBufferSize controls the maximum size of the Jet Engine’s internal cache. By default, it is set to optimize performance on most machines. It does this by reading data in 2KB pages, placing the data in a memory cache. The data in the cache is readily available to forms, reports, tables, and queries. Lowering the value for MaxBufferSize frees memory for other tasks. Lowering the value might be helpful on a machine with a minimum memory configuration.

ReadAheadPages controls the number of 4KB data pages that the Jet Engine reads ahead when performing sequential page reads. This number can range from 0 to 31, with the default at 16. The higher this number is, the more efficient Access is at reading ahead so that data is available when you need it. The lower this number is, the more memory is freed up for other tasks.

As you configure any of these settings, remember that what is good for one machine is not necessarily good for the next. The settings for each machine must be optimized with its unique hardware configuration in mind.

What Is the Access Database Engine?

Introduced with Access 2007 is the Access Database Engine. This is the new name for Jet. It includes functionality required for the new Access 2007 feature set. This engine provides the functionality necessary for the integration with Microsoft Windows SharePoint Servers 3.0. It also allows for integration with Microsoft Office Outlook 2007. Finally, this new database engine allows you to create multivalued lookup fields.

Letting the Performance Analyzer Determine Problem Areas

You can make many changes to improve the performance of an application. Most of them require significant attention and expertise on your part. The Performance Analyzer is a tool that does some of that work for you. This tool analyzes the design of an Access application to suggest techniques you can use to improve the application’s performance. Many of the techniques that the Performance Analyzer suggests can be implemented automatically.

To use the Performance Analyzer, click to select the Database Tools tab. Then select the Analyze Performance tool found in the Analyze group. The dialog box in Figure 18.4 appears.

Figure 18.4. The Performance Analyzer dialog box.

Select the individual tables, queries, forms, reports, macros, modules, and relationships that you want the Performance Analyzer to scrutinize. If you want Access to analyze the relationships, you must click the Current Database tab and then select Relationships. Make all your selections and click OK. When the Performance Analyzer completes the analysis process, the second part of the Performance Analyzer dialog box appears, as shown in Figure 18.5. This window provides you with a list of suggested improvements to the selected objects. The results are broken down into Recommendations, Suggestions, Ideas, and Fixed (meaning items that were automatically fixed). Suggested improvements will include enhancements such as the addition of an index or the conversion of an OLE object. After analyzing the CHAP18EX database included on the sample website, for example, the Performance Analyzer suggested that Option Explicit be added to the basBenchMarks module.

Figure 18.5. The second part of the Performance Analyzer dialog box.

Designing Tables to Optimize Performance

Now that you have seen the changes you can make to your environment to improve performance, take a look at the changes you can make to your data structures to optimize performance. Such changes include eliminating redundant data, using indexes, selecting appropriate field data types, and using various query techniques.

Tweaking the data structure is imperative for good performance. No matter what else you do, poor data design can dramatically degrade the performance of your application. All other optimization attempts are futile without proper attention to this area.

You can spend days and days optimizing your data. You must carefully think through and analyze these data changes. You will often make data changes over time as you or your users identify problems. Such changes can include those in the following sections.

Why Be Normal?

In essence, “be normal” means normalize your tables—that is, consolidate common data in related tables. Processing the same data that appears in multiple places can significantly slow down your application. This slowdown is the result of both the volume of data that is generated, as well as the need to update all copies of the data whenever the data changes. Suppose a company address appears in both the Customers table and the Orders table. If the company address changes, it must be changed in both the Customers table and in the Orders table. This information should be included only in the Customers table. Queries should be used to combine the address and order data when needed.

I Thought You Just Told Me to Normalize

When it comes to performance, unfortunately, there are no hard-and-fast rules. Although most of the time you gain performance by normalizing your data structure, denormalizing your structure can help at times. This generally is the case when you find yourself creating a particular join over and over again. Another example is an accounting application in which you need to be able to readily see the total amount that a customer owes. Instead of evaluating all the open invoices each time you move to a customer record, you can store the total amount that the customer owes on the customer record. Of course, this requires that you update the summarized figure whenever the customer is billed or makes a payment. In summary, you can try denormalizing the data to see whether dramatic performance improvements result. Remember that denormalization has definite downsides regarding data integrity and maintenance.

Index, Index, Index!

It is amazing how far an index can go toward improving performance. You should include any fields or combination of fields on which you search in an index. You should create indexes for all columns used in query joins, searches, and sorts. You should create primary key indexes rather than unique indexes, and unique indexes rather than nonunique indexes. It is not necessary to create an index for the foreign key field in a one-to-many relationship. Access automatically creates the index when you establish the relationship. Furthermore, there is no benefit to creating an index on a field containing highly repetitive data. An example is a state field in a customer table where all the customers are located in one of two states. Although you can overuse indexes, when you use them properly, the performance improvements rendered by indexes are profound.

Select the Correct Data Type

When defining a field, select the shortest data type available for the storage of the data. If you will be storing a code between 1 and 10 within the field, for example, there is no reason to select Double for a numeric field. Although Double would work, it would require unnecessary storage space as well as unnecessary processing time. On the other hand, make sure that you always leave room for growth of your data. For example, many people often select Integer, not realizing that they should have selected Long Integer.

Designing Database Objects to Improve Performance

There are many things that you can do to make queries, modules, forms, and reports run more efficiently. The sections that follow cover each of these objects in detail.

Optimizing the Performance of Your Queries

Optimizing your queries requires a great deal of practice and experimentation. Some queries involving a one-to-many relationship run more efficiently if you place the criteria on the “one” side of the relationship, for example. Others run more efficiently if you place the criteria on the “many” side. Understanding some basics can go a long way toward improving the performance of your queries and your application as a whole, as listed in the following:

• Include as few columns in the resultset as possible. This limits the data returned to the client if you ever convert your application to a client/server environment.
• Try to reduce the number of complex expressions contained in the query. Although including a complex expression in a query eliminates the need to build the expression into each form and report, the performance benefits gained sometimes are worth the trouble.
• Use the Between operator rather than greater than (>) and less than (<) operators. The Access Database Engine is able to process Between more efficiently.
• Group Totals queries by the field that is in the same table you are totaling. In other words, if you are totaling cost multiplied by price for each order in the Order Detail table, group by the order ID within the Order Detail table, not by the order ID within the Orders table. This reduces the number of rows that the Access Database Engine must process when running the query.

Now that you have seen what you can do with the design of your queries to improve performance, take a look at a couple of simple techniques you can use to improve the performance of your queries.

A simple but often neglected method of optimizing queries is to deliver your queries compiled. A query compiles when you open it in Datasheet view and then simply close it. If you modify a query and then save it, it is not compiled until the query runs. The Access Database Engine compiles all queries when you compact a database. Delivering precompiled queries ensures that they run as quickly as possible. It is therefore a good idea to compact a database before you distribute it to your users.

Finally, it is important that you compile your queries using the same amount of data that your application will contain. The reason is that the Access Database Engine’s Query Optimizer optimizes the query differently, depending on the amount of data it finds. If you build a query using 100 records that will run on a live table containing 100,000 records, the query will not be optimized properly. You must rerun and resave your query using the correct quantity of data if you want the query to be optimized properly, or you must compact the database after the live data has been entered.

Making Coding Changes to Improve Performance

No matter what you do to optimize the operating system environment and improve your data design, poor code can continue to bog you down. A properly optimized application is optimized in terms of the environment, data design, and code. Just as poor table design can degrade performance, poor coding techniques also can have a dramatic negative effect on performance. Changes to your code include eliminating variants and dead code, using built-in collections, and using specific object types. An important code-related optimization is to deliver your modules precompiled.

The following changes and techniques can aid in the improvement of performance. It is important to recognize that any one change won’t make much of a difference. However, an accumulation of all the changes, especially where code is being re-executed in a loop, can make a significant impact on your application’s performance.

Eliminate Variants and Use the Smallest Data Type Possible

Variant variables are the slowest for the operating system to process; they carry a lot of overhead because the compiler resolves them at runtime. Remember that this statement declares a variant type of variable:

Dim intCounter

To strong-type this variable as an integer, for example, you must modify your code to look like this:

Dim intCounter As Integer

Not only should you strong-type your variables, but you also should use the smallest data type possible. Remember that data types such as Boolean, Byte, Integer, and Long are the smallest and therefore the fastest to resolve. They are followed by Single, Double, Currency, and (finally) Variant. Of course, if you must store very large numbers with decimal points in a variable, you cannot pick Single. Just keep in mind that it is wise to select the smallest data type appropriate for the use of the variable. Listing 18.1 provides code that illustrates the difference between using a variant and a long integer.

Listing 18.1. Data Type Benchmark Illustrating the Effect of Data Type on Performance

The code, found in the form frmBenchmark in the CHAP18EX.ACCDB sample database, loops using a variant and then a long integer. The example displays the amount of time required to execute each loop, along with the percent difference between the two techniques.

Use Specific Object Types

Just as using the General variant data type is inefficient, using generic object variables also is inefficient. The reason is that the compiler needs to evaluate their type at runtime. The MakeItBold subroutine uses a generic object variable, as shown in Listing 18.2.

Listing 18.2. The MakeItBold Subroutine

On the other hand, the SpecificBold subroutine uses a specific object variable, as Listing 18.3 shows.

Listing 18.3. The SpecificBold Subroutine

The difference is that the SpecificBold routine expects to receive only text boxes. It does not need to resolve the type of object it receives and therefore is more efficient.

This code is contained in the CHAP18EX.ACCDB database on the website. You can find the example in the form called frmObjVar.

The best way to truly compare using a specific control versus a generic control is to benchmark the techniques, as shown in Listing 18.4.

Listing 18.4. Object Type Benchmark Compares Using a Specific Control and a Generic Control

The code, found in the form called frmBenchmark, passes a text box to two different routines. The first routine receives any control as a parameter. The second routine receives only text boxes as a parameter. The benchmarks prove that routines that use specific object types take less time and are therefore more efficient.

Use Inline Code

There is a tendency to call out to procedures for everything. These calls are good from a maintenance standpoint, but not from an efficiency standpoint. Each time VBA calls out to a procedure, it takes additional time to locate and execute the procedure. This is particularly evident when the procedure is called numerous times. The alternative is to use inline code. Executing inline code is more efficient than calling out to procedures because Access does not need to locate the code. The downside of inline code is that it is more difficult to maintain. You must decide how important maintainability is compared to speed.

Listing 18.5 shows the same code called as a routine and executed inline. The benchmark shows that the inline code executes much more quickly.

Listing 18.5. Inline Code Benchmark

Toggle Booleans Using Not

This code is very inefficient:

You should modify it to look like this:

bFlag = Not bFlag

Besides requiring fewer lines of code, this expression evaluates much more quickly at runtime. Listing 18.6 proves that toggling the Boolean variable is a much more efficient approach to the problem than having to test each condition separately. You can find this code in the form called frmBenchmark on the website accompanying this book.

Listing 18.6. Toggling Boolean Benchmark

Use the Built-In Collections

The built-in collections are available whether or not you use them. By using For Each...Next and a collection of objects, you can write efficient code, as shown in Listing 18.7.

Listing 18.7. Using For Each...Next

In this example, you use the Forms collection to quickly and efficiently loop through each form, changing the caption on its title bar. The code shown in Listing 18.8 illustrates the use of the Forms collection, as well as an alternative method of accomplishing the same task.

Listing 18.8. For Each...Next Benchmark

Without the For Each..Next loop, you must use a variable to loop through the forms. Notice that the code sets intNumForms equal to the number of forms in the Forms collection minus one. The loop goes from zero to the value stored in intNumForms, changing the caption of the specified form. Although the performance gains realized by using the Forms collection are not dramatic, the Forms collection technique is much simpler to implement.

Use the Len Function

Using the Len function (as shown in Listing 18.9) is more efficient than testing for a zero-length string (as shown in Listing 18.10).

Listing 18.9. Using the Len Function

Listing 18.10. Testing for a Zero-Length String

Listing 18.9 is easier for VBA to evaluate and therefore runs more quickly and efficiently. This point is emphasized by the code shown in Listing 18.11 (located in the form frmBenchmark). The code shows two loops. One uses the Len function, and the other does not. The benchmark proves that the routine that uses the Len function executes more quickly.

Listing 18.11. Len Benchmark

Use True and False Instead of 0

This example is similar to the preceding one. It is better to evaluate for True and False (as shown in Listing 18.12) instead of 0 (as shown in Listing 18.13).

Listing 18.12. Evaluating for True and False

Listing 18.13. Evaluating for 0

The code in Listing 18.12 runs more efficiently. The benchmark shown in Listing 18.14 provides an example. The lngSalary variable is evaluated against zero in the top loop. The bottom loop tests lngSalary against True. The second loop runs more quickly.

Listing 18.14. True/False Benchmark

Eliminate Unused Dim and Declare Statements

As you modify your subroutines and functions, you often declare a variable and then never use it. Each Dim statement takes up memory, whether or not you are using it. Furthermore, Declare statements, which you use to call external library functions, also take up memory and resources. You should remove these statements if you are not using them.

Eliminate Unused Code

Most programmers experiment with various alternatives for accomplishing a task. These experiments often involve creating numerous test subroutines and functions. The problem is that most people do not remove this code when they are done with it. This dead code is loaded with your application and therefore takes up memory and resources. Several third-party tools are available that can help you find both dead code and variable declarations.

Use Variables to Refer to Properties, Controls, and Data Access Objects

If you are going to repeatedly refer to an object, you should declare an object and refer to the object variable rather than the actual control, as shown in Listing 18.15.

Listing 18.15. Declaring an Object and Referring to the Object Variable

This is a scaled-down example, but if numerous properties are being changed, or if this code is being called recursively, an object variable can make the code more efficient, as Listing 18.16 shows.

Listing 18.16. Using an Object Variable to Make Your Code More Efficient

The benchmark shown in Listing 18.17 contains two loops. The first loop sets four properties of the same control, explicitly referencing the control as each property is set. The second loop uses an object variable to accomplish the same task. The difference in performance between the two loops is somewhat dramatic.

Listing 18.17. Object Variable Benchmark

Use With...End With

Another way to optimize the code in the preceding example is to use a With...End With construct, as shown in Listing 18.18.

Listing 18.18. Using With...End With

The code in Listing 18.19 shows two different loops. The first loop explicitly references the text box four different times to set four different properties. The second loop uses a With statement to reference the same control and set the four properties. The code in the second loop executes much more efficiently.

Listing 18.19. Object Variable Resolution Benchmark

Resolve Variables Outside a Loop

Although both the object variable reference and the With statement significantly improve performance, Listings 18.17 and 18.19 can be further improved by resolving the object variable outside the loop whenever possible. Listing 18.20 provides an example.

Listing 18.20. Resolving the Object Variable Outside the Loop

Notice that the object variable is resolved outside the loop. This loop executes significantly faster than the loops in Listings 18.17 and 18.19.

Use the Me Keyword

The preceding example uses Forms!frmHello.txtHello to refer to a control on the current form. It is more efficient to refer to the control as Me.txtHello because VBA searches only in the local name space. Although this makes your code more efficient, the downside is that the Me keyword works only within form, report, and class modules. It won’t work within standard code modules. This means that you cannot include the Me keyword in generic functions that are accessed by all your forms.

Use Dynamic Arrays

Array elements take up memory, whether or not you use them. It’s therefore sometimes preferable to use dynamic arrays. You can increase the size of a dynamic array as necessary. If you want to reclaim the space used by all the elements of the array, you can use the Erase keyword, as in this example:

Erase aNames

If you want to reclaim some of the space being used by the array without destroying data in the elements you want to retain, use Redim Preserve:

Redim Preserve aNames(5)

This statement sizes the array to six elements (it’s zero-based). Data within those six elements is retained.

Use Constants When They Are Available

Constants improve both readability and performance. A constant’s value is resolved after compilation. The value that the constant represents is written to code. A normal variable has to be resolved as the code is running because VBA needs to obtain the current value of the variable.

Use Bookmarks

A bookmark provides you with the most rapid access to a record. If you are planning to return to a record, set a variable equal to that record’s bookmark, making it easy to return to that record at any time. Listing 18.21 shows an example that uses a bookmark.

Listing 18.21. Using a Bookmark

You can find this code in basOptimize of CHAP18EX.ACCDB. The code stores the bookmark in a variable until the Do...Until loop executes. Then the code sets the recordset’s bookmark equal to the value contained within the variable.

Set Object Variables Equal to Nothing

Object variables take up memory and associated resources. Their value should be set to Nothing when you are finished using them. For example:

Set oObj = Nothing

Setting variables this way conserves memory and resources.

Use Action Queries Instead of Looping Through Recordsets

Besides being easier to code, executing a stored query is much more efficient than looping through a recordset, performing some action on each record. Listing 18.22 shows an example that loops through a recordset.

Listing 18.22. Looping Through a Recordset

This code, which is located in basOptimize of CHAP18EX.ACCDB, loops through a recordset, adding 1 to each project’s total estimate. Contrast this with the code in Listing 18.23.

Listing 18.23. Executing a Stored Query

This code uses a command object to execute a stored query called qupdLowerEstimate. The query runs much more efficiently than the Do...Until loop shown in Listing 18.22.

Deliver Your Application with the Modules Compiled

Applications run slower when they are not compiled. Forms and reports load slower, and the application requires more memory. If you deliver your application with all the modules compiled, they do not need to be compiled on the user’s machine before they run.

To easily recompile all modules, choose Debug, Compile with the Visual Basic Editor (VBE) active. This command opens and compiles all code in the application, including the code behind forms and reports. It then saves the modules in the compiled state, preserving the compiled state of the application.

Retain the Compiled State

Don’t bother choosing the Debug, Compile command if you plan to make additional changes to the application. An application becomes decompiled whenever you modify the application’s controls, forms, reports, or modules. Even something as simple as changing a single line of code causes the application to lose its compiled state. It’s therefore important to choose the Debug, Compile command immediately before you distribute the application.

Distribute Your Application as an ACCDE

The process of creating an ACCDE file compiles all modules, removes editable source code, and compacts the destination database. All Visual Basic code will run but cannot be viewed or edited. This improves performance, reduces the size of the database, and protects your intellectual property. Memory use also is improved.

Organize Your Modules

VBA code theoretically can be placed in any module within your application. The problem is that the compiler does not load a module until you call a function within it. After you call a single procedure in a module, the compiler loads the entire module into memory. Furthermore, if a single variable within a module is used, the compiler loads the entire module into memory. As you might imagine, if you design your application without much thought, every module in your application will be loaded.

If you place similar routines in one module, that module will be loaded, and others will not. This means that if people are using only part of the functionality of your application, they will never be loading other code modules. This conserves memory and therefore optimizes your application.

Designing Forms and Reports to Improve Performance

You can do several things to forms and reports to improve your application’s performance. They include techniques to quickly load the forms and reports, tips and tricks regarding OLE objects, and special coding techniques that apply only to forms and reports.

Designing Forms

Because forms are your main interface to your user, making them as efficient as possible can go a long way toward improving the user’s perception of your application’s performance. Additionally, many of the form techniques are extremely easy to implement.

Form-optimization techniques can be categorized in two ways: those that make the forms load more quickly and those that enable you to more efficiently manipulate objects within the form.

The larger a form and the more controls and objects you have placed on it, the less efficient that form is. Make sure that controls on the form do not overlap. It also is extremely beneficial to group form data onto logical pages. Grouping is especially important if your users have insufficient video RAM. Objects on subsequent pages should not be populated until the user moves to that page.

Forms and their controls should be based on saved queries or embedded SQL statements. Include only fields required by the form in the form’s underlying query. Avoid using Select * queries; because Access is so efficient at internally optimizing the manipulation of query results, this improves the performance of your forms. To further take advantage of the power of queries, reduce the number of records that the query returns, loading only the records you need at a particular time.

If you will use a form solely to add new records, set the DataEntry property of the form to Yes so that it opens to a blank record. This step is necessary because, otherwise, Access must read all records to display the blank record at the end of the recordset.

Avoid bitmaps and other graphics objects if possible. If you must display an image, it is important to remember that OLE objects take far more resources than images. If an OLE bitmapped object does not need to be changed, convert it to an image. To accomplish this, right-click the object and choose Change To, Image.

Avoid the use of subforms whenever possible. Access treats a subform as a separate form. It therefore takes up significant memory. Make sure that all fields in a subform that are either linked to the main form or used for criteria are indexed. Make sure that only necessary fields are included in the record source of the subform. If the data in the subform does not need to be edited, set its AllowEdits, AllowAdditions, and AllowDeletions properties to No or set its RecordsetType property to Snapshot.

Make sure that the RowSource for a combo box includes only the columns needed for the combo box. Index on the first field that appears in the combo box. This technique has a dramatic effect on the speed at which a user can move to an element of the combo box. Also, whenever possible, make the first visible field of a combo box a text field. Access converts numeric fields to text as it searches through the combo box to find a matching value. Finally, don’t base list boxes or combo boxes on linked data if that data rarely, if ever, changes. Instead, make the static table local, updating it whenever necessary.

As a general rule regarding the performance of forms, place all database objects, except data, on each user’s machine. This way, you eliminate the need for Access to constantly pull object definitions over the network.

Close forms that no longer are being used. This action is necessary because open forms take up memory and resources, degrading performance.

Another tip that can help you dramatically improve the performance of your forms is to use the default formatting and properties for as many controls as possible. By doing this, you significantly improve performance because only the form and control properties that differ from the default properties are saved with the form.

Finally, eliminate the code module from forms that don’t need it. A form without a code module loads more quickly and occupies less disk space. You can still call function procedures from an event property using an expression, or you can navigate about your application from the form using hyperlinks. You can remove the module associated with a form by setting the HasModule property to No.

Designing Reports

Many of the report-optimization techniques are the same as the form-optimization techniques. Reducing the number of controls, avoiding overlapping controls, basing reports on queries, avoiding OLE objects, and converting unbound object frames that display graphics to image controls are all techniques that improve the performance of reports as well as forms.

You can use a few additional techniques to specifically improve the performance of reports. Eliminate any unnecessary sorting and grouping expressions, and index all fields on which you sort or group. Base subreports on queries rather than on tables, and include only necessary fields in the queries. Make sure that the queries underlying the report are optimized and that you index all fields in the subreport that are linked to the main report.

A special technique that you can use to improve the performance of reports involves the No Data event and the HasData property. The No Data event is fired when a report is opened, and no data is returned by the record source of the report. The HasData property is used to determine whether a report is bound to an empty recordset. If the HasData property of a subreport is False, you can hide the subreport, thereby improving performance.

Practical Examples: Improving the Performance of Your Applications

To ensure that your applications are optimized, you can take several steps:

• Make sure that the database is compacted.
• Use the Performance Analyzer to analyze the application and make recommendations for improvement.
• Choose Debug, Compile from the VBE before distributing the application.

Summary

The most attractive application can be extremely frustrating to use if its performance is less than acceptable. Because Access itself requires significant resources, you must take the responsibility of making your code as lean and efficient as possible.

This chapter focused on several techniques for improving performance. Probably one of the easiest ways to improve performance is to modify the hardware and software environment within which Access operates. You learned about adding RAM, defragmenting a hard disk, and tuning virtual memory and other settings to dramatically improve the performance of your applications. You also looked at using the Performance Analyzer to quickly and easily identify problem areas in your application. Finally, the chapter focused on data-design fundamentals, coding techniques, and techniques to optimize forms and reports.

By following the guidelines covered in this chapter, you can help ensure that you are not inadvertently introducing bottlenecks into your application. Although any one of the suggestions included in this chapter might not make a difference by itself, the combined effects of these performance enhancements can be quite dramatic.