Achieve the Compiled State In Access Databases

Written by Dan Haught of FMS

Access Basic, as implemented in Access 2.0, was a tokenized, interpreted language. This means that module code was converted into tokens, then interpreted and executed one line at time. VBA, as implemented in Access 95 and later, is a much more complex and interesting component. Indeed, there are 12 different levels of compilation that VBA offers (these levels are notexposed to the developer which is probably a good thing since only a few people at Microsoft understand them anyway). 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/97.

Module code is saved in two 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 white space, procedure and variable names, and comments. The compiled state is the executable version of your code. All comments and white space have been 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 your application to run slower than molasses in January 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 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 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 states in the database. This means that the original source code is stored, as is the 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:

Open any module
From the Debug Menu, select Compile and Save All Modules.  

Your database is now in the compiled state. This includes form and report modules (called class modules using Access terminology) and standard   modules. All VBA code that is called by your application is immediately ready for execution. There is no need for compilation. 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
Make changes to code-bearing objects, such as form, reports and controls, or create such code-bearing objects
Rename the database (this only applies to Access 95, *not* to Access 97)
Compact the database into a different name (this only applies to Access 95, *not* to Access 97)
So the bottom line 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. 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 option 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.

Close Your Database a Couple of Times a Day

VBA dynamically loads code into memory as needed on a module level. If a function is called or a variable is used, the entire module containing that function or variable is loaded into memory. As you are developing your application, you keep loading code into memory. Visual Basic for Applications does not support dynamic unloading of these modules. Because of this, RAM will begin to fill up. To boost development performance (I.e. to decrease the amount of time you spend as a developer working on your application), you may want to close the database periodically to unload the modules. Note that you do not have to close Access itself, just the database itself. However, if you have library database code loaded, you should exit Access also.

It is especially important to close your database after a Compile All Modules command. The Compile All Modules command pulls all of your code into memory. Closing and reopening the application will unload the code and enable you to develop faster because of the additional free memory.

If you are developing your application in a single-user environment, you can improve your development performance by opening the application exclusively.  This allows Visual Basic for Applications to save and compile faster by eliminating multiple-user save situatios.

Make an MDE File

If possible, make an MDE file out of your database. An MDE file cannot become decompiled, so your Visual Basic code always runs at top speed. Additionally, since no source code is stored in the MDE file, the database loads faster and uses less memory.

Note: Search Access Help for 'MDE files'. Applies only to Access 97

Editors Note:  Delivering a MDE file is only OK if the users do not want to add their own Forms, Reports or Modules or alter any of yours.  You will always have to provide them with a new one.

This message comes to you courtesy of FMS.  Read more of this article at the following page.
http://www.fmsinc.com/tpapers/vbaint/

 Decompiling A Database

Click here to learn how to decompile a database

The Access Workbench makes it easier to decompile Click Here To Find Out More

Links >>>  Home | Search | Workbench | Orders | Newsletter | Access Security | Access professionals