Microsoft KB Archive/223447

= ACC2000: Can't Secure Individual Modules in Access 2000 =

Article ID: 223447

Article Last Modified on 1/26/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q223447



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



SYMPTOMS
You are unable to set the permissions for individual modules using the Security tools provided by Microsoft Access 2000. Permissions set to individual modules that were created in earlier versions of Access are ignored by Access 2000.



CAUSE
In Access 2000, you can no longer protect individual modules using Microsoft Jet security. The Visual Basic Environment provides protection only on a per project basis. The Visual Basic Environment cannot protect individual modules.



RESOLUTION
You can use one of two methods to protect code in your Visual Basic project. You can lock the project for viewing and assign a password. Or you can create an MDE or ADE file from the source database or Microsoft Access project file.

Locking the Project for Viewing
The Visual Basic Environment enables you to protect the Visual Basic project of an Access database or Access project file by locking it for viewing and assigning a password to it. This will only allow users who know the password to view and modify your Visual Basic code, project properties, and references. To lock your Visual Basic project for viewing and to assign a password, follow these steps:
 * 1) Start Access 2000.
 * 2) Open the Access database or Access project whose Visual Basic project you want to protect.
 * 3) Press ALT+F11 to open the Visual Basic Editor.
 * 4) On the Tools menu, click  Properties.
 * 5) Click the Protection tab.
 * 6) Click to select the Lock project for viewing check box.
 * 7) Type a password in the Password and Confirm Password boxes, and then click OK.

The next time that someone opens the database and tries to open any module or view the project's references or properties, that person will receive a prompt to enter the password for the Visual Basic project. The person must provide the password in order to continue.

Creating an MDE/ADE File
CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Saving your database or Access project file as an MDE or ADE file compiles all modules, removes all editable source code, and compacts the destination database. Your Visual Basic for Applications code continues to run, but it cannot be viewed or edited, and the size of your database will be smaller because the source code will be removed. To save your database or Access project file as an MDE or ADE file, follow these steps:
 * 1) Start Microsoft Access 2000.
 * 2) Open the database whose Visual Basic project you want to protect.
 * 3) On the Tools menu, point to Database Utilities, and click Make MDE File (or Make ADE File if you are using an Access project file).
 * 4) Type the name of the MDE file that you want to create, and click Save.
 * 5) Close the database or Access project file that is currently open in Access.
 * 6) Open the MDE or ADE file that you saved in step 4.

Note that you are unable to view or modify the design of Access forms, reports, and modules.

Protecting Only Certain Modules
Even though you cannot protect just certain modules within the Visual Basic project in the Visual Basic Environment, there is a strategy that you can use to accomplish this task. If you want some modules to be protected while others are not, determine which modules you want to protect and move them into an external Visual Basic project, and leave the modules that you want to leave unprotected in the original project. You can do this by creating a new Access database or Access project file, exporting the modules that you want to protect to that file, and then deleting those modules from the original Visual Basic project.

Then you can use one of the two methods discussed earlier to protect the external Visual Basic project. Afterward, you can add a library reference to the protected Visual Basic project. Note that a limitation to this approach is that you will not be able to directly instantiate class modules stored in the referenced project.



MORE INFORMATION
A Visual Basic project is the set of all code modules, library references, and associated properties stored within an Access database or Access project file. In earlier versions of Access, if you wanted to view or edit a particular Visual Basic module, Access only had to retrieve that one module from the database's Visual Basic project and load it into the Visual Basic editor used by Access. If you edited that module and saved it, Access only had to save the changes to that particular module. Therefore, Access only had to read and write modules on an individual basis. This enabled Access to grant or deny permissions to individual modules, rather than to the entire Visual Basic project.

With the integration of the Visual Basic Environment in Access 2000, this behavior has changed. If you want to view or edit an individual module in the Visual Basic Editor, Access 2000 must load the entire Visual Basic project into the Visual Basic Environment. From this point on, the ability to view and edit modules is controlled solely by the Visual Basic Environment and not by Access. The Visual Basic Environment can only protect the entire Visual Basic project, not individual modules within a Visual Basic project.

Steps to Reproduce Behavior

 * 1) Start Microsoft Access 2000.
 * 2) Open the sample database Northwind.mdb.
 * 3) On the Tools menu, point to Security, and then click User and Group Permissions.
 * 4) Look at the Object Type combo box list.

Note that Modules are not listed as an object type that you can secure.

