Microsoft Access 2010 Bible

Part IV: Professional Database Development

844

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 com-

piled, 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 manipula-

tions 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 pre-
vent 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 doc-

umenter” or “Access database documenter” should return valuable information.

Using the .accdb Database File Format

Since its inception, Microsoft Access has used a database engine named Jet (an acronym for Joint

Engine Technology). Beginning with Access 2007 the Microsoft Access development team wanted

to add significant new features to Access, such as multi-variable and attachment fields. Because the

new features were so significant, it wasn’t possible to retrofit Jet with the code necessary to support

the new features. As a result, Microsoft developed an entirely new database engine, the Access

Connectivity Engine (ACE), for Access 2007, 2010, and future versions of Access.

Access 2010 supports several file formats, including the following:

l Access 2007 .accdb format

l (^) Access 2002–2003 .mdb format
l Access 2000 .mdb format
l (^) Access 97 .mdb format