databasedev.co.uk - database solutions and downloads for microsoft access

Preventing Changes to your Microsoft Access VBA Code:

If your database contains Visual Basic for Applications (VBA) code procedures, you want to ensure that your users can't make changes unless authorised or qualified to do so. Access to the code can be prevented in a couple of different ways:

• Password protecting your VBA code
• Saving the database as a Microsoft Database Executable (MDE) format

When a password is used to protect the code, anyone who has access to the password can still edit the VBA. If the database is converted to an MDE file format, users using the file can run the code procedures, but they are not available to view or edit.

Applying a Password to Microsoft Access VBA Code

Here we detail how to secure the code in your database by assigning a password to protect the code:

1. In the database that you want to secure the VBA code go to Tools » Macro » Visual Basic Editor or press Alt + F11 to open the Visual Basic Editor.
2. From the Tools menu in the Visual Basic Editor window, select the properties for the database:
   
   
3. Select the Protection tab for the properties dialog box:
   
   
4. Check the Lock project for viewing checkbox.
5. In the Password box, type in your chosen password and then tab to the Confirm Password box.
6. Type in the password again to confirm and then click on OK.
   
   The password has now been set. This will become active next time that the database is opened.
7. Choose File » Close and Return to Microsoft Office Access. Close the database and exit Microsoft Access.
8. Now, when we open the database again and chose to open the Visual Basic Editor up again, the editor window opens but all that is displayed is the Project Explorer, containing the name of the project. The actual code window is closed:
   
   
9. If we attempt to open the project (in this example db1) by clicking on the + sign to the left of it we receive the following dialog box:
   
   
10. When we type in our password, the project expands to display all contained components:
    
    
    The password is only required to be entered once whilst the database is open, not each time you need to access the code window. If the database is closed and re-entered, the password is required to access the project.
11. To remove the password protection from the project, go to the Tools menu to open the properties dialog window back up.
12. Select the Protection tab as previously and clear the Lock project for viewing checkbox. Remove the two sets of passwords (indicated by the asterisks) and click OK.
13. Close the Visual Basic Editor and close the database. Next time you enter the database, you will not be prompted for a password to open the project.