Microsoft Access 2010 Bible

Part IV: Professional Database Development

842

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 dif-
ferent 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 pro-

cedure that you’re calling. In addition, all the procedures that could be called from those proce-

dures 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.)