Optimizing MS Access 97 Application Performance

  • Article
Archived content. No warranty is made as to technical accuracy. Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

OFF411

Presented at Tech-Ed 97

Dan Haught

FMS Inc.

Dan Haught has been developing database applications and tools for more than ten years on a variety of platforms and has been using Microsoft Access since its pre-1.0 days. Dan manages product development for FMS in Vienna, Virginia, where he develops products for the Total Access line, and is the author of several books on Microsoft Access and the Jet database engine.

Email: mailto:danh@msn.com**.**
https://www.fmsinc.com**.**

On This Page

Introduction
Performance Enhancements in Microsoft Access 97
Optimizing the Conversion Process
Optimizing Your Operating Environment
What's New in DAO 3.x & Microsoft Jet 3.x
What's New in DAO/Jet 3.5
Specific DAO Optimizations
Improving Form and Report Performance
Optimizing Visual Basic for Applications Performance
Code Load-on-Demand
Developing Applications with Microsoft Access and Visual Basic for Applications
Two Application Scenarios
Creating MDE Files
Using The Performance Analyzer
Conclusion

Introduction

Microsoft Access 97 represents the second 32-bit release of Microsoft Access. Whether you are converting existing applications to Microsoft Access 97, or starting new development, there are a number of strategies for optimizing and improving performance in your application.

The following topics will be discussed:

  1. Performance enhancements in Microsoft Access 97

  2. Optimizing the conversion process

  3. Optimizing your operating environment

  4. What's New in Microsoft Jet 3.0

  5. Improving form and report performance

  6. Improving Microsoft Visual Basic for Applications (VBA) performance

  7. Developing Applications with Microsoft Access and Visual Basic for Applications

  8. Using the Performance Analyzer

Performance Enhancements in Microsoft Access 97

One of the identifying characteristics of Microsoft Access 95 was is poor performance in many application development situations. Microsoft has made a large number of enhancements to address these problems.

Memory Usage

  1. The memory usage of Microsoft Access has been reduced dramatically. For example, Microsoft Access 97 with the Northwind Traders sample database loaded uses 30% less memory than Microsoft Access 95.

Form Loading

  1. Form load, a critical performance bottleneck in Microsoft Access 95, has been heavily optimized. All forms load 20 to 55% faster than Microsoft Access 95 on a 486/33.

  2. Some form load tests have shown a 10 to 30% increase in performance when compared with Microsoft Access 2.0.

  3. The algorithm for loading form items has been fine-tuned, and the addition of lightweight forms and reports helps in this area.

  4. RecordSource and RowSource queries are now saved as real, compiled queries, instead of just Structured Query Language (SQL) strings.

  5. Form and report class modules are not loaded until the first non-empty event fires.

  6. Modules are now delay-loaded, helping form load time.

  7. Various system information items are now cached.

The Compiled State

It is much more difficult to lose the compiled state. (See the section on "Improving Module Performance" later in this paper for more information on the Compiled State.) Most of the work here has to do with VBA's tracking of dependencies.

Database Bloating

Databases converted from Microsoft Access 2.0 to Microsoft Access 95 can grow in size at an alarming rate. Work has been done in this area to reduce the amount of storage needed. For example, NWIND has been reduced in size by 30%.

Application Load

Many performance optimizations have been made to improve the time to start Microsoft Access.

  1. DAO and UTILTY.MDA are delay-loaded. This means that they are not loaded until they are actually called.

  2. The workgroup information file (typically SYSTEM.MDA) is not loaded until needed.

  3. Much work has been done on swap tuning of Office and VBA.

Optimizing the Conversion Process

Microsoft Access 95 required a number of steps to ensure that converted databases would perform adequately in terms of performance. This typically required:

  • Compacting the database immediately after conversion

  • Compiling and saving all modules

Fortunately, Microsoft Access 97 is much more intelligent about its conversions. After importing and converting your objects, Microsoft Access compiles all your code and saves the compiled state. It then compacts the database to remove the temporary objects created during conversion, and reclaims unused space.

Note: When you install Microsoft Access 97, you are given the option to remove Microsoft Access 2.0 if it exists on your system. Unless you are extremely short on disk space, you may want to leave Microsoft Access 2.0 installed. In the event that Microsoft Access 97 has problems converting your database, you can return to Microsoft Access 2.0 to attempt to solve problems

Optimizing Your Operating Environment

Every application needs a good environment. You can run Microsoft Access 97 under both Microsoft Windows 95 and Microsoft Windows NT 3.51 or later. This section discusses things you can do to your operating environment to make Microsoft Access 97 run faster.

RAM, RAM, RAM

The three most effective things you can do to improve performance are:

  1. Upgrade your RAM

  2. Upgrade your RAM

  3. Upgrade your RAM

The bottom line is: this is the single most effective way to improve performance of all your Windows apps. Microsoft Access 97 is particularly affected by the availability (or lack thereof) of RAM. In fact, you could upgrade your RAM to 32 MB and be very happy with the performance of Microsoft Access 97 without following any of the other tips in this paper! It is that important.

The following table summarizes memory scenarios many developers have encountered:

RAM

Performance

4 MB

It actually runs! Microsoft Access 97 will run on a machine with 4MB of RAM. It is so slow as to be unusable, but it does eventually finish what is asked of it.

8 MB

Not much better than 4MB, but it is slightly more usable.

12 MB

Microsoft's stated minimum (16MB is the stated minimum for installations using NT as the operating system). Large application performance will probably not be acceptable with this amount of RAM.

16 MB

A good starting point for development of robust applications.

24 MB

Now we're talking! Microsoft Access 97 starts to pick up at 24 MB. This is the reasonable minimum for Windows NT installations.

32 MB

The promised land. Running Microsoft Access 97 on a 32-MB machine with at least a 75mhz Pentium processor is very nice. This is the recommended configuration for developers using Microsoft Access on a regular basis. Of course, if you are one of the chosen few how have 64MB of RAM in their machines, you are probably wondering what all the complaining is about!

Table 1 - RAM and Microsoft Access 97

If upgrading the memory on your machine (or your client's machine as the case may be) is at all an option, do it. There is no faster way to make Microsoft Access 97 go faster. If you can't upgrade the memory on your computer, don't lose heart—there are still things you can do. Read on.

Note: Several products, marketed under the generic term "RAM Doublers" purport to double the amount of available RAM through software trickery. When evaluating the use of such a product, bear in mind the maxim "You can't get blood from a stone." Indeed, there is at least one product on the market that actually slows down Windows 95. This is currently a hot topic in the trade press. Use of RAM doublers will in no way increase the performance of Microsoft Access 97. Caveat Emptor.

Using Compressed Drives

In a word, don't. Microsoft Access 97 does not provide stellar performance when run from a compressed drive. According to the README file, running Microsoft Access 97 on a drive that is compressed using DriveSpace can cause significant decreases in performance.

Running Components Across a Network

Again, don't! This is true of almost any application, but really becomes a maxim when the new 32-bit Office applications are involved. The 97 versions of the Microsoft® Office components are bigger and more numerous than ever. Its not as simple as the old days when you could run a single EXE file across the network. In modern Windows software, DLLs, OLE objects, help files, type-libraries and executables all contribute to a binary stream of data that is not happy being squeezed through a low-bandwidth network wire. Hard-disk space is cheap enough now that you shouldn't have to suffer performance problems because lack of local storage is forcing you onto a network.

Monkeying With Windows 95 Virtual Memory

Windows 95 automatically manages virtual memory (the swapfile in Window 3.1x parlance). In general, it does a good job of balancing available disk space with the amount of real memory in your system. However, some users have reported better performance by experimenting with the size of the swap file. You can modify these settings in Windows 95 by right-clicking on My Computer, selecting Properties clicking the Performance tab and pressing the Virtual Memory button. According to the Microsoft Access 97 Help File, modifying the virtual memory settings can be beneficial in two cases:

  1. You don't have enough space on your primary drive, and you want to move the swap file to another drive.

  2. You have a local disk that is faster than your primary drive, and you want to move your swap file there.

Making Windows Run Faster

There are numerous things you can do to optimize your operating system. Things like eliminating that 1024x768 24-bit color wallpaper you are using. Or turning off System Agent from the Microsoft Plus Pack when you are not using it. Or regularly (at least once a week) defragmenting your hard disks. Or replacing all your real-mode device drivers with 32-bit versions. And so on. There really are a lot of things you can do at the base level of your computer to speed things up and free more resources.

Here are a few other tips:

  1. Some users have reported memory leaks with Office 97 applications. This means that the application does not return all the memory it has used when it is closed. Consider re-starting your machine a few times throughout the day to reclaim lost resources.

  2. Turn off all non-critical applications and tools. For example, if you are using the Office toolbar, decide if you really need it. If you are using the Plus Pack for Windows 97, consider turning off high-color mode, desktop themes that have wallpaper, the system agent, or anything else that may be taking resources away from your primary work.

  3. Some users have reported that the use of OCX controls (also know as OLE custom controls) can incur a serious performance overhead. You may want to consider doing serious performance testing to ensure that the use of such controls does not cause your application to run slower.

What's New in DAO 3.x & Microsoft Jet 3.x

Jet 3.0 was introduced with Microsoft Access 95. Microsoft Access 97 uses version 3.5 of Microsoft Jet. The following Jet 3.0 coverage is included for those unfamiliar with Microsoft Access 95/Jet 3.0 performance enhancements.

In the area of data access, improvements are related to the use of the new Jet database engine 3.0. New development work on the Jet database engine was focused on performance, particularly in multi-user scenarios. Jet database engine 3.0 is completely 32-bit, and uses multiple threads of execution to provide dramatic performance gains. In fact, 60% of all data access operations through the Jet database engine are over 20% faster in version 3.0 than in version 2.0. The following Figure shows the results of some of the pre-release performance testing, with Jet database engine 2.0 performance indexed as 1.0 for all tests:

Cc767939.4111(en-us,TechNet.10).gif

Jet Database Engine 3.0 vs. Jet Database Engine 2.0 Performance

With Jet database engine 3.0, multi-user performance may be up to six times faster. The following changes to the Jet database engine enable it to achieve substantially better performance in multi-user scenarios and reveal design changes that developers can make to improve the performance of their Microsoft Access-based solutions:

  1. Fewer read locks on index pages. This will remove many locking conflict issues and remove the need for applications to call DBEngine.Idle DBFreeLocks.

  2. New mechanism for page reuse. Jet database engine 3.0 no longer recycles pages until the last user closes the database. This reduces concurrency conflicts and processing time.

  3. New mechanism for page allocation. In previous versions, pages from different tables were often mixed together on disk. In version 3.0 pages are typically allocated so that the pages from the same table are more contiguous. This scenario greatly enhances the read-ahead capabilities of the Jet database engine. This may improve search and select operations.

  4. Faster delete operations. In version 2.x, pages were doubly linked, thus causing a delete operation to visit every page when all rows were being deleted. If there is no predicate attached to the DELETE statement and no foreign keys referencing the table, this operation can now take advantage of new storage techniques and remove the table almost instantly.

  5. Improved multi-user performance. In version 2.0, multiple insert operations frequently blocked other users. Version 3.0 incorporates new locking algorithms that can significantly reduce these conflicts.

Developers should be aware that using certain constructs can cause Jet database engine 3.0 operations to be slower than in version 2.0. These are related to the use of internal Unicode interfaces in Microsoft Access 97, which enable internationalization to languages that use extended character sets. To avoid this issue, developers should use SQL updates for bulk operations involving string manipulation whenever possible. Using SQL commands will optimize string manipulations. In the following example, the first method will be slower than the second:

Sub DAOUpdate()
'This is the slower method.
Dim db As Database
Dim rs As Recordset
Dim sAddress As String
Set db = OpenDatabase("NWind.mdb", True, False)
Set rs = db.OpenRecordset("SELECT * FROM Customers",dbOpenDynaset)
While Not rs.EOF
rs.Edit
rsAddress = rs!Address
rs!Address = sAddress
rs.Update
rs.MoveNext
Wend
End Sub
Sub DAOSQLUpdate()
'This is the faster method.
Dim db As Database
Set db = OpenDatabase("NWind.mdb", False, False)
db.Execute "UPDATE Customers SET Address = Address", dbFailOnError
End Sub

What's New in DAO/Jet 3.5

Microsoft Jet 3.5 is introduced in Microsoft Access 97. Although it does not have nearly as many performance enhancements as the previous version, the following items are new:

New MaxLocksPerFile Setting

There is a new registry setting called MaxLocksPerFile. This allows you to improve performance of the completion of large queries when the data being operated on is on a NetWare or Windows NT-based server. This forces a transaction to only partially commit.

The SetOption Method

You can use the new DBEngine.SetOption method to set various Jet engine parameters at runtime. This allows you to set/test/reset various values without having to restart Microsoft Access and Jet.

Other Improvements

  1. Indexed columns exhibit improved multiuser concurrency—that is, more users can read and update indexed columns without getting locking conflict messages

  2. Large queries now run faster because of improvements in the transactional behavior for SQL data manipulation (DML) statements. New registry settings are available that force transactions to commit when a specified lock threshold is reached.

  3. Queries that use the inequality <> operator in their criteria statements are now faster.

  4. Sequential reads may be faster, as the Jet database engine can now preallocate up to 64 KB of disk space at a time.

  5. Temporary queries now run faster.

  6. Deleting a table is faster when you use the SQL DROP statement of the SQL DELETE statement without a predicate.

ODBCDirect

DAO 3.5 offers a new technology called ODBCDirect. This feature allows you to deploy client/server applications using Microsoft Access 97 without using Microsoft Jet as the translation layer. ODBCDirect is an interface directly to ODBC, and as such, is faster in many operations.

With ODBCDirect, you have the following advantages (from the Microsoft Access 97 Help File):

  1. ODBCDirect can make your code faster and more efficient by giving you direct access to ODBC data source. Since it doesn't require loading the Microsoft Jet Database engine, ODBCDirect consumes far fewer resources on the client side. The ODBC server is responsible for all query processing.

  2. ODBCDirect gives you improved access to server-specific functions that aren't available by using ODBC through Microsoft Jet. For example, for servers that support cursor specification, ODBCDirect allows you to specify where cursors are located, whether locally or on the server. In addition, to interact with stored procedures at the server level, you can specify input values and check return values, which you can't do when using Microsoft Jet.

  3. ODBCDirect also supports asynchronous queries. When you execute a query, you don't have to wait for a query to finish running before you begin another operation. You can track the query' execution by checking the StillExecuting property.

  4. ODBCDirect supports batch updating, enabling you to cache Recordset object changes locally and then submit these changes to the server in a single batch.

  5. With ODBCDirect, you can create simple cursorless result sets, or more complex cursors. You can also run queries that return any number of result sets. You can limit the number of rows returned and monitor all the messages and errors generated by the remote data source without affecting the performance of the executing query.

Specific DAO Optimizations

Don't Use Transactions for Performance

One of the typical things that Microsoft Access 1.x and 2.0 developers have done is to wrap update code in transactions for performance gains. That is, any code operations that add, modify, or delete data are enclosed in a transaction using the BeginTrans...CommitTrans pair. While this offered dramatic speed increases in performance in version 2, Jet 3.0 has been optimized to do this automatically.

If you have code that uses transactions for speed optimizations, try removing the transactions and let Jet handle the buffering. You may see speed increases. Note that you may also see speed decreases. As with most things in this paper, the behavior of your applications depends on many complex interactions with your hardware, OS settings, and your database.

Optimize Jet Through Registry Settings

There are a number of settings that you can create and modify in the system registry that control how Microsoft Jet works. The settings are rather complex, and their interactions with Microsoft Access require some trial and error work. All of the settings are documented in the Microsoft Jet Database Engine Programmers Guide from Microsoft Press.

Improving Form and Report Performance

When Microsoft Access 2.0 first appeared, almost everyone was excited by the new events, methods and properties it introduced. But almost as quickly as most users had ripped the shrink-wrap off the boxes, they noticed that their forms loaded slower than they did in Microsoft Access 1. People complained vociferously about how bad Microsoft Access 2.0 was in terms of performance. Sound familiar? Its almost funny how history repeats itself.

After the complaining stopped, developers began to realize that there were a number of ways that they could improve the performance of form loading. Indeed, many developers now agree that Microsoft Access 2.0 is really the epitome of what is good in desktop database products. So before you despair about Microsoft Access 97, look at things in context, take a deep breath, and see what you can do to improve things.

Compiling Code

Unlike Microsoft Access 2.0, it is possible to save form and report module code in a compiled state in Microsoft Access 97. As you may recall from your version 2.0 work, Microsoft Access used to compile all form and report module code each time the form or report was loaded. With Microsoft Access 97, it is possible to save the form or report's code in a compiled state, eliminating the need for Microsoft Access to perform the compilation step each time the form is loaded. For this to work effectively, it is critical to understand the concept of the compiled state and how to maintain it. For more information on this, see "Improving Module Performance" later in this paper.

Using the New Image Control to Handle Bitmaps

Forms and reports can use the new Image control in Microsoft Access 97. This control is more efficient at loading, handling, and displaying bitmap images than the bound and unbound frames available in Microsoft Access 2.0. If you are using bitmaps on your forms, consider using this new control to make things run faster.

Using the New Tab Control Instead of OCX Controls

Microsoft Access 97 contains a native tab control. If you application uses and OCX control for tabs, consider changing to the native control. Since an OCX will no longer be required, load time for the form should be faster.

Introducing Lightweight Forms and Reports

Microsoft Access 97 introduces the concept of lightweight forms and reports. A lightweight object is one that has no module associated with it. For example, if a form has no module behind it, it is considered lightweight. When the form is loaded, no module needs to be loaded, and VBA does not need to be loaded. Therefore the form loads faster.

As soon as you create a module in a lightweight form, it is no longer lightweight. Note that since there are very few forms that you create as a developer that don't call code or have an associated module, this new feature is not considered a developer enhancement.

User-Interface Techniques:

  1. Avoid overlapping controls. This is particularly true when using transparent controls that overlap non-transparent controls. Microsoft Access has to do a lot of work to manipulate the pixel bits to get transparency. While this optimization has been around since Microsoft Access 2.0, it has added currency in Microsoft Access 97 because text boxes can now be transparent.

  2. Simplify the form's design by reducing the number of controls on the form. Control loading can be a very expensive proposition in terms of performance. For each control, a chunk of properties need to be loaded and evaluated, the events must be initialized, and the control must be painted on the form. In Microsoft Access 2.0, it was common to place hidden controls on forms to act as "holding-places" for values. This was typically done to control modal dialog interactions, or to get around the fact that you cannot reference global variables from a form other than in code. In Microsoft Access 97, you can probably replace these extraneous controls with the new Property Let and Property Get procedures and achieve the same results.

  3. Consider using multi-page forms, separated by the page-break character. This allows you to present only the controls needed, and can reduce form-load time. Microsoft Access 97 gives you several new properties that make it easier to work with multi-page forms, so this technique is even more accessible than before.

  4. Finally, if you can't make it run faster, at least make it look faster. The judicious use of status messages and progress meters can hide a multitude of sins.

Form Data Retrieval Optimization Techniques

It should go without saying that data loading can be one of the most time-consuming tasks when opening a form. While this may seem obvious, you would be amazed how much work can be done on a form's RecordSource to improve performance. Here are some of the more important query techniques. (Note that these techniques can result in better data retrieval speeds throughout your application, not just for forms):

  1. Reduce the number of fields that your query returns. Only bring across the fields that are absolutely necessary for the current operation. Don't use SELECT * queries unless you really need to display or access all the fields in the underlying table(s).

  2. Reduce the number of records that your query returns. Consider using a WHERE clause in your query to limit the number of records returned.

  3. Use indexes on fields that are frequently searched on. You may also want an index on any field that your form's records are sorted on. While this one is obvious, what may not be so obvious is that over-use of indexes can slow down data operations such as inserts or deletes. Use them judiciously.

  4. Compile your saved queries before delivering your application. Do this by opening them in datasheet view and immediately closing them. Doing so will save the query plan with the query. This plan tells Jet how to process the query in the most efficient way. If you can, avoid using SQL strings in module code that are constructed and run on the fly. When these are run, they have to be compiled. Also be aware that, over time, the plan saved with the query may become obsolete as the data in the MDB file changes. You may want to recompile queries after compacting your database.

  5. Consider using the "Open Forms as Hidden" technique. To do this, open the most commonly used forms in hidden mode when your application starts. See the BACKFORM.MDB database for an example of this technique.

Improving Report Response Time

While there are no Microsoft Access 97-specific report optimizations that I can offer (except for techniques reports share with forms, such as using the Image control to display bitmaps), there is one new event for reports in Microsoft Access 97 that can speed things up. This is the NoData event. This event is fired if a report is opened, and there is no data for that report. In Microsoft Access 2.0, the only way to detect the absence of data was to independently run the query driving the report behind the scenes, and see if it returned any records. Needless to say, this was a highly inefficient (and slow) way of doing things. With the NoData event, you can effectively speed up this part of your database application.

Optimizing Visual Basic for Applications Performance

Introducing Visual Basic for Applications

Microsoft Access 97 includes Visual Basic for Applications. Visual Basic for Applications, which replaces Microsoft Access Basic, provides several key benefits for developers and end users, including a common language across applications, a well-known paradigm for Windows-based development, and broad industry support. This section will help developers understand how Microsoft Access 97 and Visual Basic for Applications can work together effectively to provide good performance in projects that use a significant number of Visual Basic for Applications code modules.

This section begins by describing the key differences between Visual Basic for Applications and Microsoft Access Basic and by discussing the advantages of developing with Visual Basic for Applications. Next, it provides an overview of the commands and terminology of Visual Basic for Applications and discusses how these commands affect the development process. Finally, it describes in detail actions that developers can take to improve the performance of their Microsoft Access-based solutions.

Microsoft has worked to minimize the impact on developers of having a new language in Microsoft Access 97. Some changes in development approaches are necessary, but this work will be offset by the benefits of using a standard language and in faster applications for this and future versions of Microsoft Access.

Using a Common Language Across Applications

The vision for a common language across applications was first expressed by Bill Gates in a 1987 Byte Magazine article. By providing a standard customization and development language across a suite of products, Microsoft will enable developers and users to customize applications and build solutions rapidly and efficiently. Contrast this with a world of products, each of which requires a separate learning curve in order to achieve even the simplest results.

Today, Microsoft has integrated Visual Basic for Applications into four key applications: Microsoft Excel, Microsoft Project, Microsoft Visual Basic 4.0, and now, Microsoft Access. Developers can invest in Visual Basic for Applications and be confident in their ability to leverage the capabilities of each of these products. In the future, Microsoft plans to integrate Visual Basic for Applications into Microsoft Word and Microsoft® PowerPoint®. Even today, where applications do not directly host Visual Basic for Applications, many of them expose their object models through OLE, and are therefore programmable in Visual Basic for Applications. Examples of these applications include Microsoft PowerPoint for Windows 95, Microsoft® Schedule+ for Windows 95, and Microsoft Office for Windows 95 Binders.

Code Load-on-Demand

In Microsoft Access 2.0, all Microsoft Access Basic code modules are loaded into memory at startup. Visual Basic for Applications loads code only as it is needed by the application. For example, when Microsoft Access 97 opens, only the modules that are called during startup are loaded into memory. Load-on-Demand enables applications with large amounts of code to run faster.

Visual Basic for Applications loads code at the module level. When a function is called, all code in the module containing that function is opened. Furthermore, when a variable is used, the module that contains the variable declaration is also opened.

Developing Applications with Microsoft Access and Visual Basic for Applications

This section gives an overview of the commands and terminology of Visual Basic for Applications in Microsoft Access and how these commands are used in the development process.

Compile All Modules

This command is found on the Debug menu in a module's Design View. The Compile All Modules command opens and compiles all code in the application including code behind forms and reports. Executing this command is the only way to fully compile your application. You should use the Compile And Save All Modules command (discussed below) to save the compiled state of your application.

The Compile All Modules command should be used only when necessary. During development, applications will be decompiled almost all the time. It is a waste of time to carry out a Compile All Modules command unless you are ready to distribute your application or check end-user performance. The Compiling Loaded Modules command (discussed below) will check the syntax of your code without taking the time to compile modules that have not changed.

There is a special case where you may want to use a Compile All Modules command. After converting an application from a previous version of Microsoft Access, you will want to carry out a Compile All Modules command to find 16-bit library calls and other code that requires modification for Microsoft Access 97.

Compile and Save All Modules

This command is found on the Debug menu in a module's Design View. After a Compile All Modules command, carrying out a Compile and Save All Modules command saves the fully compiled state. If you close the application without saving, the compilation state will be lost.

Compile Loaded Modules

This command is found on the Run menu in a module's Design View. The Compile Loaded Modules command carries out the same command as clicking the Compile button on the toolbar. Unlike the Compile All Modules command, the Compile Loaded Modules command only compiles the modules that are currently open, including code behind open forms and reports. Even if you compile all modified code with this command, the application may not be fully compiled. You must use the Compile All Modules command in the application at the same time to ensure a complete compilation.

During the development process, the Compile Loaded Modules command is the fastest method of checking Visual Basic for Applications syntax.

Losing the Compiled State

Microsoft Access can store Visual Basic for Applications code in one of two states: compiled and decompiled. An application becomes decompiled whenever the application's controls, forms, reports, or modules are modified. The application decompiles whenever you add a new control, form, report, or module; create new Code Behind Form (CBF) or Module; or delete or rename a control, form, report, module, or CBF module. If you do not save the modification, the compilation state of Visual Basic for Applications is preserved on disk.

Developers should be aware that in Microsoft Access 95, if an application is renamed, the name-space of the Visual Basic for Applications code is modified and the application will decompile. This means that if you compact your application into a new name, the compilation will be lost. To prevent this, rename the compacted application back to the original name before running it. Because Microsoft Access 97 does not use the database name for the project name, renaming the database no longer decompiles the project. Use the Tools|Options|Advanced dialog to view and/or modify the project name.

Decompiled Code

Applications run more slowly when decompiled. If the application is decompiled, Visual Basic for Applications must recompile the code on-the-fly as needed. This causes forms and reports to open more slowly than they do in the fully compiled state. Before the form or report can open, Visual Basic for Applications must compile the code behind the form or module and any module code that is called by the form code.

Decompiled applications also use more memory than compiled applications. If the application is compiled, only the compiled state is loaded into memory. For decompiled applications, the decompiled code is loaded into memory; then, the compiled state is created and loaded as needed. Both the compiled and decompiled states are stored in memory.

Decompiled applications use less disk space than compiled applications. If you import all of your application objects into a new database, all Visual Basic for Applications code will be decompiled. Following this with a compact operation will reduce the application's size by deleting the compiled state.

Dynamic Loading and Application Development

Visual Basic for Applications dynamically loads code into memory as needed on a module level. If a function is called or a variable is used, the entire module containing that function or variable is pulled into memory. As you are developing your application, you will pull more an more code into memory. Visual Basic for Applications does not support dynamic unloading of these modules. Because of this, RAM will begin to fill up. To boost development performance, you may want to close the application periodically to unload the modules.

It is especially important to close your application after a Compile All Modules command. The Compile All Modules command pulls all of your code into memory. Closing and reopening the application will unload the code and enable you to develop faster because of the additional free memory.

If you are developing your application in a single-user environment, you can improve your development performance by opening the application exclusively. This allows Visual Basic for Applications to save and compile faster by eliminating multiple-user save situations.

Compile On Demand

Before you distribute your application, it is a good idea to compile all modules to check for syntax errors. VBA's compilation behavior is affected by the Compile On Demand setting available under the Tools|Options|Modules dialog.

When the CompileOnDemand option is turned on, uncompiled modules are not compiled until VBA needs to load them for execution. If this option is turned off, an uncompiled module is compiled when a module that contains a procedure that calls the uncompiled module is loaded.

Performance of your VBA code may be enhanced if you leave this option on (the Microsoft Access 97 default.)

The Call Tree

In Microsoft Access 95, when you call a procedure, VBA must load that procedure's module, as well as all the modules that contain procedures called by the original module. This is called the potential call tree, and resulted in poor VBA load performance in Microsoft Access 95. In Microsoft Access 97, modules are loaded only when they are needed. In other words, if a procedure is called, only the module containing the procedure is loaded. This should enhance the performance of your application.

Two Application Scenarios

This section discusses the following two basic application scenarios:

1. Static Applications

These are applications that do not allow the end user to modify or to create new controls, forms, reports, or modules. Examples of static applications are wizards and other add-ins.

2. Dynamic Applications

These are applications that modify or create forms, reports, or modules. This modification can be done either by the end user or by the application itself. An example of a dynamic application is an inventory management system where the application creates new reports.

The steps to improve the performance of static applications apply also to dynamic applications.

Scenario 1: Static Applications

This section discusses the performance issues that affect applications that are not in danger of becoming decompiled after distribution to end users. These performance topics concern good development practices that will result in faster applications, regardless of whether the application is static or dynamic.

Creating Efficient Modules

The first development practice that will speed performance is to minimize the amount of code in each module. When a form (or report) opens, it must load the code in all modules called by the form; therefore, relocating unnecessary functions from your modules will improve form-open performance.

Reducing Number of Modules Opened

The second development practice that will speed performance is to minimize the number of modules opened by a form or report. When a form opens, it must load into memory all modules called by the form and all modules that contain declarations of the variables used in the code behind the form. Each module opened by the form must, in turn, open any modules it requires. Your application will run faster (and be easier to manage) if your module call paths are simple. Global routines called by many other modules should be partitioned into separate modules to prevent unnecessary code from being loaded into memory. The same is true of global variables. Loading a module that uses a global variable will cause the module containing the global variable declaration to be loaded.

Opening Modules at Run Time (Application.Run)

You can design your application to load code at run time by using the Run method of the Application object. To call a function in this manner, replace the existing function call

MyFunction(parameter 1, parameter 2,...)
with:

Application.Run "project.MyFunction", parameter 1, parameter 2, ....
where project is the name of the MDA file containing the function.

In the MyFunction case, MyFunction is loaded whenever the form or module containing the function call is opened. In the Application.Run case, MyFunction is loaded only if and when it is called by the form or module.

This technique is used in the design of Microsoft Access Wizards. A significant percentage of the wizard code is not called by each wizard and the largest block of code is run after the Finish button is clicked. By using the Application.Run syntax, the unneeded code is never loaded, and the large block of code is not loaded until the user clicks the Finish button. If the user clicks Cancel before the wizard task is finished, the largest block of code is not loaded.

The Application.Run syntax is also useful when a command button on a form runs a large amount of code, but the button is not selected by many users or is not selected often. If the button calls a subroutine using the Application.Run method, the code is not loaded when the form is loaded; it is loaded only when the button is clicked.

There are some restrictions to using the Application.Run method, as follows:

  1. The project file must be open or located in the Addinpath specified for wizards in the Registry. For more information, please consult the product documentation or online Help.

  2. The project file must have an MDA extension if it is included in the Registry.

  3. You cannot pass parameters with user-defined data types by using the Application.Run method. To work around this limitation, call a shell function with no parameters and pass the user-defined data types by using global variables. The shell function can then call the function that requires user-defined data types.

  4. Microsoft Access ignores any value returned by a procedure called by the Application.Run method.

Scenario 2: Dynamic Applications

This section discusses the performance issues that are unique to applications that allow end users to modify, create, or delete controls, forms, reports, or modules. These applications will normally remain in a decompiled state, but developers can maximize performance by following the suggestions below.

Saving Space by Not Compiling

If end users will make frequent changes to the application, there is little point in distributing a fully compiled application. The extra space required for the compiled state will be useless as soon as the application becomes decompiled. To delete the compiled state completely, import all of your application objects into a new database, and then compact the new application.

Separating Decompiled Code from Global Modules

If a significant portion of the application code is contained in modules (as opposed to CBF), application performance can be improved by moving modules to a code library and creating a reference from the application front end. This will allow end users to add new forms and reports without decompiling the library database. There is some overhead in creating the reference to the library, and end users do have to manually establish the library references; therefore, this method should be used mainly for applications with a significant amount of module code. In an application designed in this manner, a form open will compile only the necessary code in the front end and quickly open the library containing the fully compiled module code.

No Circular References

There is a design consideration when moving code into libraries. Circular references are not supported in code libraries. That is, a module cannot call any modules that call functions or variables in the original module. This is true no matter how many intermediate modules are in the chain of references.

Creating MDE Files

Microsoft Access 97 offers the exciting new capability of creating a "Compiled-Only" version of your database. This version (known as an MDE file) is a copy of your database, but it contains none of your VBA code in the source form. Instead, only the compiled version of the VBA code is included in the database.

If you have applications that use VBA code, you can benefit from this new feature. The process of creating and MDE does the following:

  1. Creates a copy of your database

  2. Copies all non-module objects to the new database

  3. Copies the compiled form of your VBA code to the new database

  4. Compacts the new database.

This offers the following benefits:

  1. The MDE file is smaller since the source code is not contained in the database.

  2. The MDE file should run faster, since it never can become decompiled.

  3. The module code in the MDE file version is completely secure. Because there is no source code to hack into or steal, security is not an issue.

Once you have created an MDE file, the follow actions are disallowed:

  1. Viewing, modifying, or creating forms, reports or modules.

  2. Adding, deleting, or changing references to object libraries or databases.

  3. Changing code using the properties or methods of the Microsoft Access or VBA Object models.

  4. Changing the MDE database's project name using the Options dialog box.

  5. Importing or exporting forms, reports, and modules to or from the MDE. Tables, queries and macros can still be manipulated.

It is important to note that when you make an MDE file, you still keep your original database for development work. Think of it this way: your original database is your source code, and the MDE version is the compiled executable.

Using The Performance Analyzer

The Performance Analyzer was a direct result of usability studies conducted with Microsoft Access 2.0 users. We evaluated a number of Microsoft Access 2.0 applications and discovered that many built-in performance features were not being used. Based on this research, we created the Performance Analyzer, a tool which analyzes the design of a Microsoft Access-designed application and suggests a number of techniques to improve the performance of that solution. The Performance Analyzer can then implement any or all of the suggestions automatically.

The Performance Analyzer was designed using our knowledge of how the Jet database engine and the Microsoft Access development environment are designed. Developers can choose to analyze database components, such as tables, forms, or an entire database. The Performance Analyzer is also a way for developers to learn additional performance techniques that they can apply to their own custom applications.

By implementing the suggestions provided by the Performance Analyzer, users and developers may realize performance improvements ranging from 20% to orders of magnitude.

Conclusion

Microsoft Access 97 offers powerful new tools for building high-performance custom solutions. Using the techniques explained in this session will help developers maximize performance in their specific development situations.

Pentium is a registered trademark of Intel Corporation. NetWare is a registered trademark of Novell, Inc.