MS-Access / Getting Started

---

Understanding Module Load on Demand

One of the great features of Visual Basic for Applications (VBA), the core language of Microsoft Access, is its load on demand functionality. Using load on demand, Access loads code modules only as they're needed or referenced. In early versions of Access, on-demand loading of modules wasn't fully realized because referencing a procedure in a module loaded the entire module's potential call tree (all the modules containing procedures that might be called by the procedure). With Access, the load on demand feature truly does help reduce the amount of RAM needed and helps your program run faster.

Tip: Because Access doesn't unload code after it has been loaded into memory, you should periodically close your application while you develop. When developing, most of us have a tendency to open and work with many different procedures in many different modules. These modules stay in memory until Access is closed, which can lead to performance degradation.

Organizing your modules

When any procedure or variable is referenced in your application, the entire module that contains the procedure or variable is loaded into memory. To minimize the number of modules loaded into memory, you need to organize your procedures and variables into logical modules. For example, it's a good idea to place all global variables in the same module. If only one global variable is declared in a module, the entire module is loaded into memory. By the same token, you should put only procedures that are always used by your application (such as start-up procedures) into the module containing the global variables.

Note: In the discussion that follows, the term procedure is used to mean either a function or a sub.

Pruning the call tree

The call tree for a procedure contains any additional procedures that the current or procedure has referenced within it, as well as those referenced by the newly loaded procedures, and so on. Because a procedure may reference numerous additional procedures stored in different modules, based on the action taken by the procedure, this loading of all potentially called procedures takes a lot of time and memory.

Remember that when a procedure is called, the entire module in which that procedure is stored is placed in memory.

Therefore, a potential call tree consists of all the procedures that could be called by the current procedure that you're calling. In addition, all the procedures that could be called from those procedures and so forth are also part of the potential call tree. For example:

1. If you call procedure A, the entire module containing procedure A is loaded.
2. Modules containing variable declarations used by procedure A are loaded.
3. Procedure A has lines of code that call procedures B and C - the modules containing procedure B and procedure C are loaded. (Even if the call statements are in conditional loops and are never executed, they're still loaded because they could potentially be called.)
4. Any procedures that could be called by procedure B and procedure C are loaded, as well as the entire modules containing those potential procedures.
5. And so on and so on.

Fortunately for all Access developers, this complete loading of a potential call tree has been addressed in Access 2010. Access now automatically compiles modules on demand, instead of loading the entire potential call tree.

Note: You can turn off the Compile on Demand option if you prefer, making Access compile all modules at one time. You do this in the VBA program rather than in Access. (Access links directly to VBA's development environment for working with VB code.)

To check the status of the Compile on Demand option, follow these steps:

1. In the VBA editor window, choose Tools → Options.
   The Options dialog box appears.
2. Select the General tab, and either check or uncheck the Compile On Demand check box.
3. Click OK.

With the Compile on Demand option selected, Access loads only the portion of the call tree required by the executed procedure. For example, if you call procedure A in module A, any modules that contain procedures referenced in procedure A are loaded and compiled. However, Access doesn't take into consideration procedures that may be called from other procedures in module A, and it doesn't look at the potential call tree of the modules loaded because one of their procedures is referenced in procedure A. Because Access loads modules one level deep from the executed procedure's immediate call tree- and not the module's call tree - your applications should load and execute somewhat faster than they did in previous versions.

Even though Access has made a significant improvement in the way modules are loaded and compiled, you can still reduce the number of modules loaded and compiled. For example, never place infrequently called procedures in a module with procedures that are called often. Be aware, though, that organizing your procedures like this might make your modules less logical and harder to conceptualize. For example, you might have a dozen functions that perform various manipulations to contact information in your application. Ordinarily, you might make one module called modContacts and place all the contact-related procedures and variables into this one module. Because Access loads the entire module when one procedure or variable in it is called, you might want to separate the contact-related procedures into separate modules - one for procedures that are frequently used and one for procedures that are rarely called.

Tip Keep in mind that all modules with procedures that are referenced from a different module are loaded when the procedure is called. In your application, if any of your common procedures reference a procedure that isn't frequently used, place the infrequently used procedure in the same module as the common procedures to prevent a different module (containing the uncommon procedure) from being loaded and compiled. You may even decide to use more than two modules if you have very large amounts of code in multiple procedures that are rarely called. Although breaking related procedures into separate modules may make your code a bit harder to understand, it can greatly improve the performance of your application.

To take full advantage of Compile on Demand, you have to carefully plan your procedure placement. Third-party tools can be invaluable for visualizing where all the potential calls for various procedures are located. An Internet search with Google, Bing, or Yahoo! for terms such as "Microsoft Access documenter" or "Access database documenter" should return valuable information.