16.4. Secure VBA Code
There are many reasons to protect your Microsoft Visual Basic for Application (VBA) code. You may wish to prevent persons who are not experienced with the design of the code from introducing errors. You may also wish to conceal your intellectual property by preventing anyone from viewing the code. Or, you may want to ensure consistent application of business rules by preventing unauthorized changes. And, of course, there is the concern about hackers. Access provides two primary methods for protecting the code: securing a project and making an MDE file.
16.4.1. Securing Modules by Securing the Project
You can secure Visual Basic code from viewing or editing by locking the project from viewing and setting a password on your project. You set the project password while viewing the project in the Visual Basic Editor (VBE). When a project has a password and is locked for viewing, the password must be entered in the VBE before the Visual Basic code can be opened. The password is requested only once for each time that database is opened, and it is only requested if there is an attempt to access the code using the VBE.
Setting a password and locking the project from viewing also prevents changing the HAS Module property on forms and reports until the password has not been entered in the VBE. This is because Access needs to open the code before it can delete it, or add to it.
Setting a password and locking the project from viewing does not prevent users from changing event properties on forms or reports. Specifically, a user can remove the [Event procedure] setting from an event, causing the code to not be executed. Obviously, this could be disastrous if the code executes a critical action when that event occurs. Although users clear the [Event procedure] setting, they cannot add a new [Event procedure] to an event. However they can specify [Event procedure], and if a procedure already exists for the event, it will be called.
Figure 16.7. Figure 16-7
To prevent users from making form or report layout changes or changing the properties on events, you can generate an MDE file from your database and distribute an MDE file instead of an MDB file. Another way to prevent changes is to institute user-level security and not grant modify permissions to selected users or user groups. Both of these methods are discussed later.
16.4.1.1. Lock Project from Viewing
To prevent unauthorized access to the VBA code, lock the project from viewing and set the module password to open your project in the VBE. In the VBE, select the project in the Project Explorer. Then select Tool | project Properties. (project is the name of your project, that is, your database). This displays the Project Properties dialog box for your project, as shown in Figure 16-8.
Select the Protection tab as shown in Figure 16-9.
Figure 16.8. Figure 16-8
On the Protection tab, check the box next to Lock project for viewing. If this option is selected, you are required to enter a password. Specify a password in the Password text box and reenter the password in the Confirm Password text box. Click OK to save the password.
To test the module password, return to Microsoft Access and close the database. Reopen the database, then open the VBE, and select the project. The project Password dialog box displays requesting the password.
16.4.1.2. Removing a Module Password
To remove the lock on the module, open your project and select your project in the Project Explorer. Then select Tool | project Properties. (project is the name of your project, that is, your database). This displays the Project Properties dialog box for your project.
Then select the Protection tab. Uncheck the box next to Lock project for viewing.
Note: You do not have to clear the password. If you do not clear the password, the next time the database is closed and opened you will be prompted to enter the password to set properties for the project, but will not be prompted for the password to display the code.
Figure 16.9. Figure 16-9
NOTE
Be sure you store the password information so that you can retrieve it later. If you do not have the correct password, you will not be able to access the VBA code. Many developers choose to create a backup of the file before securing the code. This is also inexpensive insurance just in case the file becomes corrupted or the password fails. What? Did we really mean that a valid password might someday fail to allow access to the code. You bet. And, that can be incredibly frustrating and time consuming if there isn't a current backup of the file.
16.4.2. Compiling to Make an MDE File
If your database has a user interface where you control the user's experience, you may wish to prevent the user from making any kind of changes to code or other objects. An MDE file can accomplish this.
When you make an MDE file, all of your code is compiled and removed from viewing. The compile process also compacts the database, which makes the database much smaller. Compiling also optimizes the code and provides faster execution.
Before you can make an MDE file using Access 2003, you must open the database exclusively. You must also convert older databases to Access 2002 format. Access 2002 and 2003 share the same database format.
A few words of caution: If your project has references to other databases, you must make MDE files of those other databases and update the references to them in your project before you make an MDE of your project. Ah, you may be noticing a bit of a snowball effect here.
16.4.2.1. Converting a Database to 2002 Format
The title bar of the Database window indicates the database file format. Notice the Access 2000 file format in Figure 16-10.
Figure 16.10. Figure 16-10
If your database is the 2000 format, convert it to the 2002-2003 format before making the MDE.
Tip: If you wish to always create databases in the 2002-2003 format, you can change the default file format through the Advanced tab of the Tools | Options dialog box.
To convert your database to the 2002 format, select Tools Database | To Access 2002-2003 File Format... as shown in Figure 16-11.
Figure 16.11. Figure 16-11
The Convert Database Into dialog box displays options for you to specify the destination file for the new format. Enter the name of the destination file and click Save. The conversion begins immediately.
After you have converted the database (to the 2002-2003 format), it can no longer be shared with Access 2000 or Access 97 users.
With the database in the 2002-2003 format, you can now build an MDE file. Be aware that when you create an MDE, you will not be able to import, export, create, modify, or rename any forms, reports, pages, or modules in the MDE file itself. Therefore, you should retain the original MDB file in the event changes need to be made in the future. Obviously, at that time, you will need to make the changes to the MDB, and then create a new MDE to distribute.
To begin the Make MDE file process, select Tools | file . . . as seen in Figure 16-12.
Figure 16.12. Figure 16-12
The Save MDE As dialog box displays for you to specify the destination file for the MDE. Enter the name of the file you wish to create and click Save. The MDE file will be created.