Compiling

One of the final issues that we will discuss in this chapter is the ability to protect your source code by saving a database as an MDE file. An MDE file is, in essence, a pre-compiled version of the database. But before we look at MDE files in more detail, let's just take a moment or two to see how Access projects are compiled.

We saw earlier in Chapter 12 how compiling our code can alert us to any errors we may have made in our code. Some errors can be detected by Access as we are writing our code. For example, let's suppose we type the following line of code in a module:

   DoCmd openform "frmSwitchboard"   

As soon as we try to move off this line of code onto a new line, Access will alert us with a message box informing us that the line of code contains a syntax error.

In this case, the error was generated because we omitted the period between the words DoCmd and OpenForm . Despite the misleading message box that Access displays, this isn't really a compile error; it is a syntax error. By default, Access will check the syntax of every line of code you enter and flag any errors it notices like the one above. If you want to, you can disable this automatic syntax checking by unchecking the Auto Syntax Check box under the Tools Options dialog.

Be wary of disabling automatic syntax checking. There are occasions when it can be useful to disable it. For example, you might be pasting a large chunk of code from, let's say, Access Basic into the module window and you know it will need a fair bit of rework before it will work in VBA. In that case you won't want message boxes appearing every time you move from one line to another.

But in most other situations, you will want automatic syntax checking enabled. Most of the errors it determines are genuine mistakes and are much more easily corrected on the spot.

Whereas syntax errors are easily recognizable as soon as they occur, there are other types of errors that can creep into our code that cannot be detected until later on. For example, what if we create a Do...Loop structure and forget to put the Loop statement at the end? We certainly wouldn't want Access to flag the error when we move off the line containing the Do statement. So when should these errors be detected? The answer is that these are compile errors and are detected when VBA attempts to compile the project. We'll look now at what compilation actually involves and the implications of the different types of compilation afforded to us by VBA. Then we'll look at ways of compiling our code programmatically.

What is Compilation?

So what happens when a VBA project is compiled? Normally when programmers talk about compiling an application, they mean that the human-readable code is converted into native machine-readable code that can be executed directly. Access works slightly differently, in that the VBA code that we write is not converted directly into machine-readable code but is instead converted into an intermediate format called p-code (or pseudo-code). When the application is run, the p-code is interpreted (translated into machine-readable code also called native code or machine code - the code that the particular processor inside your PC actually uses) line-by-line by a run-time DLL.

Many developers regard p-code as an unnecessary evil and bemoan the performance degradation that results from VBA not being compiled into native machine code. In point of fact, although native code can be substantially faster than interpreted p-code for computationally - intensive operations, which rearrange lots of bits and bytes all over the place, most of the VBA code we write is no slower in p-code than native code. After all, the VBA functions we use already reside in a run-time library which is highly optimized machine code, so there won't be too much overhead there. In any case, once you start calling subprocedures, DLLs, or other objects, the overhead of setting up things like stack frames makes the difference between p-code and native code performance negligible. Add to that the fact that the average application spends less than 5% of the time running code and you will see that the p-code versus natively compiled argument doesn't hold that much water when it comes to VBA projects in Access.

All the same, the process of compilation still causes the code we have written to be checked for syntax and integrity; and it's that stage of the process that is most noticeable to us. If structures without an End If , variables that haven't been declared, calls to procedures that don't exist - these are all the types of error that are detected and flagged to us when we compile our code. In fact, the whole process of checking the syntax and integrity of the code and then compiling it into p-code can be quite lengthy, especially where large amounts of code are involved.

If compiling code takes time and highlights errors in our code, the corollary is that trying to run uncompiled code will be just as slow (because the code will have to be compiled when it is run) and may contain bugs . So, get into the habit of regularly compiling your code and always compile it before you distribute a finished application.

How do we Compile Code?

Compiling a project is a simple enough process. The easiest way is to simply choose the Compile < ProjectName > item from the Debug menu in the VBE.

This menu item is only available whenever the project is in an uncompiled state. If the project is fully compiled, the Compile < ProjectName > item is disabled on the menu bar.

Personally, I prefer to put a button on the toolbar that allows me to see whether my code is compiled and to compile it with just a single click. To do this, select Customize from the Add or Remove Buttons on the VBE toolbar:

Then, simply locate the Compile Project button from the Debug category and drag it onto the toolbar.

Lo and behold, you now have a toolbar button that allows you to compile your code at a simple click and - much more usefully - whose status indicates whether a project is compiled or not.

In previous versions of Access this button was on the toolbar by default. Its removal in the current version of Access is a consistency issue; because none of the other implementations of VBA (in Word, Excel etc.) have historically had this button on the toolbar it has been removed from VBA in Access. Frankly, I would prefer to have seen it added to the toolbar by default. After all, the first thing that all the developers I know do when they install Access is to add the button anyway!

Compile On Demand

You may have noticed two options - Compile On Demand and Background Compile - on the General tab of the Tools Options dialog in the VBE.

If VBA needs to execute a procedure and the procedure is in a module that is not compiled, it will automatically compile the module before it runs the procedure. It has to because VBA code is not machine-readable, so the VBA has to be converted into p-code that can then be interpreted into a machine-readable format.

If the Compile On Demand option is checked, then VBA will load and compile only those modules that contain procedures that are potentially called by the about-to-be-executed procedure.

However, if the Compile On Demand option is not checked, then VBA will load and compile all modules in the project. This can cause a notable performance hit, if your database contains a lot of uncompiled code, so you are advised to leave the Compile On Demand option checked at all times.

If on-demand compiling is enabled, you can also use the Background Compile checkbox to instruct VBA to use idle time to compile a project in the background, so reducing the number of times that you will need to compile your project explicitly.

When does Code Decompile?

By this stage, you might be wondering whether the Compile On Demand option really makes much difference. Surely if you compile your code before you ship your application you won't have any decompiled code, so you won't have to worry about how long it takes to compile. Most of the time that's fine. However, there are one or two things that your users can do that may cause code in your application to decompile. These include:

• Adding a form, report, control, or module

• Modifying the code in a form, report, control, or module

• Deleting a form, report, control, or module

• Renaming a form, report, control, or module

• Adding or removing a reference to an object library, or database

If any of these occurs and the project needs to be recompiled, your users could experience a significant delay if they then have to wait for the entire project to be recompiled. In such a case, selecting the Compile On Demand option could make a big difference to perceived performance.

Using MDE files

A feature introduced in Access 97 was the ability to save a database as an MDE file. When you save a database as an MDE file, Access creates a new database, into which it places a copy of all the database objects from the source database except for the modules. It then compiles all of the modules in the source database and saves them in their compiled form in the target database, which it then compacts. The target database does not contain a copy of the source VBA code, only compiled p-code. Obviously it takes time and effort to create the MDE file and so you would normally only do this when you are ready to deploy the application to the users.

There are three main benefits to be gained from using an MDE file instead of the database file:

• The p-code is smaller than the source VBA code, so the MDE file will take up less space and therefore have a smaller memory footprint when running and be easier to distribute to the target machine(s).

• The modules are already compiled, so performance of the database will be optimal.

• Users are unable to perform certain modifications to MDE files (see Restrictions on MDE Files) and they have no access to the source (uncompiled) code. This presents excellent opportunities for tightening the security of your application.

Note, however, that an MDE is not an executable file. In other words, it cannot be run as a standalone application in the way that an .exe file can be - you still need Access to be installed on every machine that will use the file.

Saving a Database as an MDE file

Saving a database as an MDE file couldn't be easier. But you must meet certain prerequisites before Access will allow you to do so. The prerequisites are as follows :

• You must use a workgroup information file that contains users defined to have permission to access the database.

• You must be logged on as a user with Open and Open Exclusive permissions for the database.

• You must be logged on as a user with Modify Design or Administer privileges for tables in the database (or you must own the tables in the database).

• You must be logged on as a user with Read Design permissions for all objects in the database.

If you use the replication features of Access, you should also note that you cannot convert a replicated database to an MDE file until you have removed all of the replication system tables from the database.

If all these criteria are met, then you can save the database as an MDE file. The process of creating an MDE file requires that Access should be able to exclusively lock the database, so you should make sure that no one else is using the database as well.

Then you simply select the Make MDE File option from the Database Utilities item on the Tools menu:

In the Save MDE As dialog that then appears, you should type the name and location that you want for the new MDE file, and that's it!

Restrictions on MDE Files

We noted earlier on that there are various actions that will cause code in a database to decompile. Because MDE files can only contain compiled code, those actions that cause code to decompile are not allowed in a database that has been saved as an MDE file. In other words, users cannot perform any of the following actions in databases saved as MDE files:

• Add a form, report, control or module

• Modify the code in a form, report, control or module

• Delete a form, report, control or module

• Rename a form, report, control or module

• Add or remove a reference to an object library or database

• Change the Project Name for the database in the Tools Options dialog

However, users are free to import or export tables, queries, and macros to or from MDE or non-MDE databases.

When using MDE files, you should also note that future versions of Access may not be backward compatible with Access 2002 MDE files. In other words, if you create an MDE file with Access 2002, you may not be able to open it or run it in future versions of Access, nor may there be a way to convert it to newer versions of Access. In practice this should not be too great a problem as you can always recreate the MDE file using the new version and redeploy it; providing that everyone is using compatible versions that is.

These two limitations - the fact that you cannot modify forms, reports , or modules, and that MDE files are not guaranteed to be upgradeable - should make you realize the importance of hanging on to your source code. When you save a database as an MDE file, you should always make sure that you keep a copy of the original source database, because you will need it if you want to make any changes to the design of forms, reports, or modules or if you want to use the database with future versions of Access.

Using MDEs with Add-Ins and Libraries

A final consideration applies if the database that you are saving as an MDE file contains references to other databases as either add-ins or libraries. In short, before you save a database as an MDE file, you should save as MDE files any databases to which the source database contains references, and then redirect the references to the MDE files rather than to the original add-ins or libraries. Only then can you save the original database as an MDE file. The example below should clarify the situation.

Let us suppose that we have a database DB1.MDB, which contains a reference to DB2.MDB, and that DB2.MDB in turn contains a reference to DB3.MDB (stage 1). The first step is to save DB3.MDB as an MDE file (stage 2) and then set a reference from DB2.MDB to the new compiled DB3.MDE (stage 3). Once you have set the reference, you can then save DB2.MDB as an MDE file (stage 4) and set a reference to it from DB1.MDB (stage 5). Only once you have done that, can you save DB1.MDB as an MDE file (stage 6).

Encrypting

There remains one final method of preventing unwanted access to the data in a database and that is to encrypt the database. Although methods such as protecting databases or projects with passwords and setting security on DAO objects are useful methods of limiting access to a database, there is nothing to prevent someone from simply opening up an Access database in an editor such as Notepad. In fact, you can get a lot of data out of an Access database just by looking at the raw data in a basic text editor like Wordpad as we have below:

In order to prevent this low-level access, you can encrypt a database. This is simple enough to do. Just close down the database that you want to encrypt and select Encrypt/Decrypt Database from the Tools Security menu in Access.

You will be prompted for the name of the database you want to encrypt and the name of the file you want to save the encrypted database as.

Encrypted databases can be used from within Access just like unencrypted databases but their contents are indecipherable when viewed as plain text. There is a slight overhead in terms of performance (Microsoft claims no more than 15% slower) incurred when using a database in an encrypted state and you will need to bear this in mind if you decide to encrypt database applications for which execution speed is a priority.