Making Microsoft Access 95 Run Faster

by Dan Haught
Vice President of Product Development
http://www.fmsinc.com

Introduction

This document is intended to share some of the information I have gathered over that last few months about making Access 95 run faster. If you take an Access 2.0 database, convert it to Access 95, and open it up and start working, chances are you are not going to be enamored with its performance. However, there are several things you can do to make your databases run faster under Microsoft Access 95.

The contents of this paper are my own personal observations. I do not have access to the minds of the developers who wrote Access 95, nor do I have "inside information" from which I draw my conclusions. Rather, this paper is the result of working with Access 95 for some time, and talking with other informed users and developers. I've also culled the useful bits from published Microsoft whitepapers and conference talks presented by members of the Access 95 team.

Some of the topics here may help you. Some may prove to be dead wrong on your system. There are no warranties, expressed, implied or otherwise as to whether or not any of the information in this paper is accurate, or even helpful. It is merely my attempt to share what I have learned.

So, Is Access 95 Faster or Slower than Access 2.0?
Correctly Converting your Access 2 Databases
Optimizing your Operating Environment
Improving Data Access Times
Improving Form and Report Response
Improving Module Performance
Using the Access 95 Performance Analyzer
Acknowledgments and Additional Resources

 

Back to Main Technical Papers Page

So, Is Access 95 Faster or Slower than Access 2.0?

Well, it depends on who you talk to! Access 95 is a very new product in many ways. It contains a completely new database engine (Jet 3.0). It has had its Access Basic programming language ripped out and replaced by the shared VBA engine, and is implemented as a 32-bit application running under a completely new set of operating systems. Additionally, Access 95 is now an OLE server. As such, its helpful not to think of Access 95 as a simple upgrade. Behind the scenes, there are many differences.

If your initial impression is that "overall, Access 95 is really slow", you are wrong. But that's OK. That was my first impression too. You should be aware that there are many areas of Microsoft Access 95 that are actually substantially faster than 2.0.

The key area of performance improvement in Access 95 is data access. In pure data operations, such as adding, editing and deleting data, without user-interface issues, performance is much better than in 2.0. Other operations, particularly those that involve user-interface interactions, such as starting Access, opening a form, or running one of the built-in wizards, may indeed be slower than in Access 2.0. This document helps explain why some of these operations are slower than expected.

To put this into perspective, let's look at the things that contribute to making Access 95 slower. (Note that this is my own interpretation of how things work. It is not the result of any serious testing or analysis, and may or may not be true):

Access 95 is a 32-bit application

Many of us have read marketing material that tells us that 32-bitness is the next great thing for performance. In truth, porting an application to a 32-bit version offers no inherent speed increases, unless the product is rearchitected at the same time to incorporate multiple threads of execution. If anything, many operations will indeed be slower, since the application now needs to handle items that are twice as big. So the bottom line is that the act of porting a 16-bit application to a 32-bit application does not in and of itself make it faster. At this point you may be wondering what the big deal is concerning porting to 32 bits. Well, 32-bitness gives developers much more flexibility because of the increased size of data structures. For example, the "Query Too Complex" messages you may be used to in Access 2.0 should be gone in Access 95.

Access 95 contains a shared programming language, called VBA

In Access 1.x and 2.0, the built-in language, called EB or Embedded Basic, could be finely optimized to work within the Access environment with great speed. If someone on the Access team needed a performance optimization, they could probably modify EB without worrying about affecting other applications. With the integration of VBA, which is shared by a host of other Microsoft applications, Access can no longer define how the language integrates. The result of this is that VBA, while adding many new features, cannot be changed at will by the Access team to address their own particular performance issues. Another issue is that Access Basic was written (to a large extent) in assembly language. VBA is written in C/C++. While this makes it easier for Microsoft to add the features that we developers ask for, C does not always have the same speed advantages as assembly language.

One of the benefits of having a shared language among applications is that it consumes less memory on your machine if you are running multiple applications that use the language. For example, if you run both Access 95 and VB4, various VBA libraries can be shared, consuming less memory. Of course, the whole integration thing is not quite complete. If you load Excel 95, you will be using the '94 version of VBA. If you also load Word 95, you will be using WordBasic. These non-VBA '95 components each consume their own amounts of memory.

Access 95 makes much more use of OLE

Its forms are much closer to the OLE standard than in previous versions. As such, the overhead associated with OLE, coupled with the fact that Access is now an OLE server, can contribute to a perceived lack of performance. However, the benefits of OLE become apparent the first time you need to open an Access 95 form from Excel 95, run an Access 95 report from VB, or place a TreeView control on an Access 95 form. All these operations require the new OLE plumbing.

New features usually slow down an application. As a user of Access, you have probably often wished for a new event, method or property that would solve a particular problem. Like any successful product, Microsoft Access is market-driven. Its features are defined by what people ask for. But the penalty paid for each new feature is two-fold: increased resource size, and decreased speed. For example, every new event, property and method that Microsoft adds in response to user requests increases the time a form takes to load. The moral of the story is: you can't have your cake and eat it too.

Microsoft Access runs under Microsoft Windows 95

While Windows 95 offers a wealth of performance and stability improvements over its 16-bit ancestors, it is nonetheless a new operating system. Microsoft simply has not had time to explore all the performance enhancements and techniques available through this new operating system. The developers of the Access 2.0 version had the luxury of writing for an operating system (Win 3.1x) that had been around for a long time. All the ins and outs of writing for this OS were pretty much known when Access 2.0 was developed. The Access 95 developers probably didn't have the same wealth of knowledge.

In order to become more internationally portable, VBA uses Unicode as its internal character standard, as opposed to Access Basic, which used ANSI. Unicode requires two bytes to represent a character, whereas ANSI only uses one. Because of this, VBA needs twice as much space to store string data which can cause a performance penalty. Also, Windows95 only supports ANSI. Since VBA uses Unicode, any string functions must convert their strings to ANSI, pass them on to Windows 95, and then convert them back to Unicode for VBA. In this regard, Windows NT (which supports Unicode) may offer a negligible performance gain for string handling.

Let's throw one more thing into the mix: the complexity of Microsoft Access as an application as it relates to all the other components it relies upon. While considering the following diagram, imagine the scenario of Access 95 opening a form. It goes something like this (greatly simplified):

Access 95 Components

Access 95 makes an internal call to load a form. It does this by calling Jet directly, which reads the form as a binary object from the database. Access takes this binary data and converts it into a displayable form. It then calls Jet, supplying the value of RecordSource property. Jet, in turn, makes operating system calls to read the data from the database file stored in the file system. It passes this information back to Access in chunks, which Access then displays. Meanwhile, the VBA code from the form object is read by Jet from a binary field and passed back to Access, which passes it on to VBA for evaluation, compilation and execution. VBA sees DAO code and uses OLE to call the DAO DLL which in turn calls Jet to return the requested information. In the meantime, Jet itself is calling VBA to evaluate the expressions that have been passed to it via DAO. The end result is a simple form displayed on your monitor.

Conclusion

So back to the question: is Access 95 really slow? My answer is a qualified "no". While certain areas of application performance, namely some user-interface actions may be slower in Access 95 as compared to Access 2, data access performance is significantly faster. A properly designed application can leverage this behavior to run at acceptable speeds. After investing a small amount of time in learning how Access 95 works, you may find this newest version of Access meets almost all of your needs.

Where Do I Go From Here?

Now that you understand some of the basic architectural differences between Access 2 and Access 95, you probably want to dive right in and get your installation of Access 95 to run faster. The remainder of this paper focuses on specific things you can do to:

• Improve your Operating Environment (tweaking the O/S)
• Improving Data Access times (getting the most out of Jet)
• Improving Form and Report response (keeping the users happy)
• Improving Module performance (code-meisters, rejoice)
• Running the built-in Performance Analyzer (hey, its free!)

Correctly Converting your Access 2 Databases

One of the things that can lead to an initial impression that Access 95 is slower is convert your Access 2 database into Access 95 and immediately start running it. This is not a good approach. There are some design changes in Access 95 that can lead to a converted database that is not in the best shape for optimum performance.

Once of the first things you probably noticed was the size of your database after conversion. This is due to a number of factors, including a new storage format for module code, a new Jet engine and a different MDB file format. While it is inevitable that your database will grow in size when converted to Access 95, you should Compact your database immediately after converting it to get an accurate picture of its size. Note that if you are working with replicated databases, you need to compact twice to completely pare the database down to its minimum size.

Note: Unlike Access 2.0's Access Basic language, VBA stores a compiled version of module code in your database. If you have a 2.0 database that contains lots of code, it will be substantially larger once it is converted to Access 95. For more information, see the section entitled "Improving Module Performance" later in this document.

The steps that I typically follow when converting an Access 2 database to Access 95 are:

1. Ensure all the module code in the Access 2 version is compiled. This helps Access 95 convert the code and can reduce the number of compilation problems that may end up in the converted code. Do this by:

• Starting Access 2.0 and opening the database to be converted
• Opening all forms, reports, and modules.
• Issuing a Compile All command from the Run menu.
• Saving all the forms and reports
• Closing all objects

1. Repair and compact the 2.0 database from Access 2.0.
2. Convert the database into Access 95 using the Convert command from the Tools menu, or by opening the database and choosing the Convert option (both available within Access 95).
3. Close the database. Repair and compact it from Access 95.
4. Re-open the database under Access 95. Open a module. Do Compile All Modules from the Run menu. Immediately after this, do Save All Modules from the File menu.

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

Optimizing your Operating Environment

Every application needs a good environment. You can run Access 95 under both Windows 95 and Windows NT 3.51 or later. This section discusses things you can do to your operating environment to make Access 95 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. Access 95 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 Access 95 without following any of the other tips in this paper! Its that important.

The following table summarizes memory scenarios I have encountered:

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 Access 95 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 Access 95. Caveat Emptor.

Using Compressed Drives

In a word, don't. Access 95 does not provide stellar performance when run from a compressed drive. According to the README file, running Access 95 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 95 versions of the 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 Access 95 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.

A Hundred and One Ways to Make Windows Run Faster

OK, so I lied. I'm not really going to give you a hundred and one ways to make Windows run faster. But I do want to point out that there are probably more than a hundred and one ways 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:

• Some users have reported memory leaks with Office 95 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.
• 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 95, 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.
• 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.

Improving Data Access Times

As mentioned earlier, there are numerous performance improvements in Microsoft Jet version 3.0 (the database engine that powers Microsoft Access). Indeed, the list of enhancements is far too long to go into here. See the Resources section at the end of this paper for other sources of information. This section outlines some of the more important Jet 3 things I have found.

Don't Use Transactions for Performance

One of the typical things that 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.

Optimizing 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 Access require some trial and error work. All of the settings are documented in the Microsoft Jet Database Engine Programmers Guide from Microsoft Press.

The Last One Out Loses

As a result of one of the Jet 3.0 performance optimizations, you may see what appears to be strange behavior in multi-user situations. In order to speed up multi-user database access, Jet attempts to delay certain housekeeping actions until the database closes. This means that the last user who exits a database in a multi-user environment will take longer to get out than any previous user. While this is not something you can control, you should be aware that this partial slowdown is the result of far greater gains in concurrency and performance while the database is open.

Improving Form and Report Response

When 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 Access 1. People complained vociferously about how bad Access 2 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 Access 2.0 is really the epitome of what is good in desktop database products. So before you despair about Access 95, look at things in context, take a deep breath, and see what you can do to improve things.

Compiling code

Unlike Access 2.0, it is possible to save form and report module code in a compiled state in Access 95. As you may recall from your version 2 work, Access used to compile all form and report module code each time the form or report was loaded. With Access 95, it is possible to save the form or report's code in a compiled state, eliminating the need for 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.

Use the new image control to handle bitmaps

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

Generic Techniques

There are a slew of generic techniques that you can use to make your forms open and run faster. These are not specific to Access 95, but are very useful nonetheless. As with the Jet 3.0 enhancements mentioned earlier, they are too numerous to be fully included here. Be sure to check out the Resources section at the end of this paper for more information.

At the risk of boring you with a rehash of the Access 95 Help File, here are a few form optimization techniques that I have found particularly useful:

User-Interface Techniques:

• Avoid overlapping controls - This is particularly true when using transparent controls that overlap non-transparent controls. Access has to do a lot of work to manipulate the pixel bits to get transparency. While this optimization has been around since Access 2, it has added currency in Access 95 because text boxes can now be transparent.
• 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 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 Access 95, you can probably replace these extraneous controls with the new Property Let and Property Get procedures and achieve the same results.
• 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. Access 95 gives you several new properties that make it easier to work with multi-page forms, so this technique is even more accessible than before.
• 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):

• 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).
• Reduce the number of records that your query returns. Consider using a WHERE clause in your query to limit the number of records returned.
• 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.
• 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.

Improving Report Response Time

While there are no Access 95-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 Access 95 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 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.

Improving Module Performance

If your database contains anything but a non-trivial amount of code, your application is guaranteed to run slowly until you understand how VBA works in Access 95. In my brief personal experience with Access 95, I have seen many complaints about speed that are directly attributable to the lack of understanding of this crucial aspect of Access 95. To be fair, there is little or no documentation included with Access 95 that explains these topics. I will endeavor to document them here.

The Importance of the Compiled State

Access Basic was a tokenized, interpreted language. This means that module code was converted into tokens, and interpreted and executed one line at time. VBA, as implemented in Access 95 is a much more complex and interesting component. Indeed, there are 12 different levels of compilation that VBA offers (these levels are not exposed to the developer unfortunately). VBA compiles your code into an executable format. Although it is not a native code compiler like C or Pascal, it is compiled to be run in an executable format through the virtual machine that VBA defines. While all this is interesting, the important thing to understand is that there are fundamental differences in the way you should think about your module code in Access 95.

Module code is saved in two important states in your Access database: the source state, and the compiled state. The source state consists of the contents of your actual modules, with full text including whitespace, procedure and variable names, and comments. The compiled state is the executable version of your code. All comments and whitespace is removed, and a stream of executable instructions has been produced--the code is ready to be run. The difference between these two states can cause performance problems if you don't understand them.

When you run a procedure, VBA checks to see if the module containing the procedure is compiled. If it is, VBA simply runs the code. If it is not compiled, VBA compiles it by loading the code into memory, performing a syntax check, and compiling it into an executable format. If all these operations succeed, it can then run the code. You probably surmised that this process of compiling is not a free lunch-it does take some time. And herein lies the crux of the matter: compiling code takes time, and compiling lots of code takes lots of time.

So if you want your database to run as fast as possible, your task is obviously to reduce the amount of time Access 95 spends compiling your code to a bare minimum. In fact, in an ideal application, all your code should be compiled and saved in the compiled state. So how do you go about this? Your Access 95 database (or project in VBA parlance) is said to be in a compiled state when all modules, including form and report modules, are saved in both their forms in the database. This means that the original source code is stored away, as is the tokenized, compiled version. In such a state, Access runs much faster, because it can completely bypass the compilation process.

Getting your database into the compiled state is actually rather easy:

1. Open a module
2. From the Run menu, select Compile All Modules
3. After compilation is complete, immediately select Save All Modules from the File menu.

Your database is now in the compiled state. All VBA code that is called by your application is immediately ready for execution. There is no need for compilation. You may also find the following VBA function useful in automating this process:

Function CompileMyApp()
    Dim cCompileLoadedModules, cCompileAllModules, cRunMenu, cVBMenuBar, cSave
    cCompileLoadedModules = 10
    cCompileAllModules = 11
    cSave = 4
    cRunMenu = 4
    cVBMenuBar = 5
    DoCmd.OpenModule ("anyModule")
    DoCmd.DoMenuItem cVBMenuBar, cRunMenu, cCompileAllModules, , acMenuVer70
    DoCmd.DoMenuItem cVBMenuBar, acFile, cSave, , acMenuVer70
    MsgBox "We have compiled and saved!"
End Function

This is all fine and well, but is just as easy for your database to become decompiled. When you make certain changes to your database, it automatically becomes decompiled, which means that the compiled state that you created using the previous steps no longer exists.

How to Avoid Decompilation

So how do you avoid decompilation, or loss of the compiled state? Any of the following actions can decompile your database:

• Modify any module code
• Programmatically make changes to code-bearing objects, such as form or reports, or create code-bearing objects
• Rename the database
• Compact the database into a different name
• Copy the database to a new name (in which case the copy would be decompiled, not the original)

So the offshoot of this is: to avoid decompilation, don't do the above. Its not as bad as it seems. After all, your database does not need to be in a compiled state while you are doing development work on it-it only really requires the performance benefits of the compiled state when it is actually running on your user's workstations. Therefore, if you follow these guidelines, you can enjoy peak performance from your module code:

• During development, don't use Compile All Modules from the Run menu. It is a waste of time, because the first time you make any changes to the module, it will decompile, or reverse the effect of Compile All Modules. Rather, use the Compile Loaded Modules instead. This action only compiles the modules that are called by the modules you have open. This is a much quicker operation, and results in the same syntax checking that Compile All Modules does.
• When you are ready to deliver your database for testing or live use, put it into the compiled state using the steps outlined above.
• If you compact your database into another name, be sure to rename it back to its original name after the compaction is complete. This will ensure that the database remains in the compiled state.

Dynamic vs. Static Apps

The above discussion of the compiled state assumes that you will always want a static database application. That is, your application will never do anything that can cause decompilation. This is typical of add-in utilities and library databases. However, in many database applications, you or your users will do things that cause the application to become decompiled. For example, if your application or your application's users create forms, reports or modules on the fly, the database will become decompiled. This is an example of a dynamic app. Because of the requirements of the application, you can never maintain a compiled state.

The VBA Call Chain

Regardless of whether your code is compiled or not, Access must still load some form of the code to execute it. Obviously, if it is compiled, Access only has to load the compiled version. But there is still a load operation involved. Which brings us to the next item of VBA optimizations: the VBA call chain. The concept of the call chain is required because VBA procedures can call other procedures, which can in turn call other procedures. This linking, from one procedure to another, forms the call chain. For example, if Procedure A() calls procedure B(), which in turn calls procedure C(), the call chain would contain:

A( ) 

B( ) 

C( ) 

You can see that the call chain shows how each procedure calls each other procedure while code is running. The key point here is to understand that if A(), B() and C() are all in the same module, only one module needs to be loaded at runtime. However, if these three procedures are in three different modules, each of those three modules needs to be loaded. Additionally, every module that any one of those modules call is loaded as well, until the code in memory has no unresolved references or loose ends. Now since loading one module is faster than loading three modules, you can probably see the optimization technique here, which is: Restructure your code to group functions that call each other into single modules.

While this may seem like a lot of work (and indeed it is for large, complex projects), it can pay off in reduced load and execution times.

Another thing to be aware of is that once VBA loads module code, it is not unloaded until you exit Access. This means that it pays to structure your code so that no unnecessary code gets loaded.

Don't Bother Un-Installing Library Databases to Reclaim Memory

With Microsoft Access 2.0, it was common practice to un-install library databases to free up more memory. In version 2, all global module code occupied the same limited memory space, whether the code existed in your database, or in one of the library databases. By un-installing unused library databases, you could free up memory. Also, since Access didn't have to load the library databases at startup, it loaded faster.

In Access 95, changes to the VBA referencing model have rendered this optimization technique obsolete. In Access 95, library code is demand-loaded which means that it is only loaded when it is needed. Because of this, un-installing Wizards, Builders and Add-Ins has no real effect on the amount of memory used by Access 95, nor does it speed up the loading of Access 95.

It is important to note that while Access 95 supports demand-loading, it does not support automatic unloading. This means that once you have opened a library database (either by invoking a wizard, builder or add-in, or by calling VBA code in a referenced database), the link to that library database remains open until you exit Access 95. Even though you can use the Add-In Manager from the Tools menu in Access 95 to un-install an add-in, the resources used by that add-in are not released until you exit Access.

Using the Access 95 Performance Analyzer

Access 95 has a useful performance tool built right into the product. From the Tools menu, select Analyze|Performance. The Performance Analyzer allows you to select all objects or specific objects, and then runs an analysis looking for potential problems.

The real value of this tool is that it takes a close look at your table data and queries and makes good suggestions about making them run faster. Tips provided for forms and reports is less thorough, and tips for macros and modules are limited to one or two pointers of limited value.

While is definitely a "version 1" implementation, the technology used is exciting in that it points to future of having applications become smarter about performance optimizations.

Acknowledgments and Additional Resources

Acknowledgments

Although this paper was put together very quickly, several people took time out of their busy schedules to provide invaluable feedback and additional tips. I would like to thank Ken Getz, Mike Gilbert, Michael Groh, Mike Gunderloy, Bruce Loehle-Conger and Jim Sturms for their contributions.

Resources

Despite the fact that Access 95 is brand-spanking-new, there are already many resources available for information in improving performance. This section lists these resources.

Back to Main Technical Papers Page

Copyright © 1998, FMS Inc. All rights reserved. This information may not be republished, reprinted or retransmitted in any form without the express written permission of FMS Inc. The information provided in this document is provided "as is" without warranty of any kind.