Understanding the Compiled State

Understanding how Access performs Compile on Demand is critical to achieving maximum performance from your Access application. However, it's also paramount that you understand what compilation is and what it means for an application to be in a compiled state.

Access has two types of code - code that you write and code that Access understands and executes. Before a VBA procedure is executed, the code must be run through a compiler to generate code in a form that Access understands - called compiled code.

Access lacks a true compiler and, instead, uses partially compiled code and an interpreter. A true compiler converts source code to machine-level instructions, which are executed by your computer's CPU. Access converts your source code to an intermediate state that it can rapidly interpret and execute. The code in the converted form (compiled code) is known as being in a compiled state.

If a procedure is called that isn't in a compiled state, the procedure must be compiled and the compiled code passed to the interpreter for execution. In reality, as previously stated, this doesn't happen at the procedure level, but at the module level. When you call a procedure, the module containing the procedure and all modules that have procedures referenced by the called procedure are loaded and compiled. You can manually compile your code, or you can let Access compile it for you on the fly. It takes time to compile the code, however, so the performance of your application suffers if you let Access compile it on the fly.

In addition to the time required for Access to compile your code at runtime, uncompiled programs use considerably more memory than compiled code does. When your application is completely compiled, only the compiled code is loaded into memory when a procedure is called. If you run an application that is in a decompiled state, Access loads the decompiled code and generates the compiled code as needed. Access doesn't unload the decompiled code as it compiles, so you're left with two versions of the same code in memory.

Even on computers with large amounts of installed memory, loading both the compiled and uncompiled versions of modules takes more time than loading compiled modules alone. There is one drawback to compiled applications: They use a bit more hard drive space than their decompiled versions because both the compiled and decompiled versions of the code are stored on the hard drive.

Hard drive space shouldn't often be a problem, but if you have an application with an enormous amount of code, you can save hard drive space by keeping it in a decompiled state. Remember that a trade-off is made between hard drive space used and the performance of your database. Most often, when given the choice, a user would rather give up a few megabytes of hard drive space in exchange for faster applications.

Tip: You can use this space-saving technique to your advantage if you need to distribute a large application and your recipients have a full development version of Access. By distributing the uncompiled versions, you need much less hard drive space to distribute the application, and the end users can compile it again at their location. If you're going to do this, you should put the entire application into a decompiled state.