Using Security Features in Access

Protecting Source Code with an MDE or ADE File

If your primary security concern is protecting your Visual Basic for Applications (VBA) code and the design of your forms and reports, you can save your MDB file as an MDE file, or your ADP file as an ADE file. An MDE file is a Microsoft Access database file with all modules compiled and all editable source code removed. An ADE file is an Access project file with all modules compiled and all editable source code removed.

Saving your database as an MDE or ADE file creates a separate copy of the database that contains no VBA source code and is smaller than the original database. Your code is compiled and continues to run, but it can’t be viewed or edited. Additionally, users can’t view or modify the design of forms, reports, and modules. Users can still view and modify the design of your database’s relationships, tables, queries, and macros after you have saved the database as an MDE file; however, you can establish user-level security if you want to protect the design of these objects.

Similarly, saving an ADP file as an ADE file doesn’t protect the design of database diagrams, tables, views, stored procedures, and macros; but you can establish security on the server itself to protect any of these objects except macros.

Saving your database as an MDE or ADE file also prevents users from performing the following actions:

• Creating forms, reports, or modules.
• Adding or deleting references to object libraries or databases by using the References command (Tools menu).
• Using the Object Browser.
• Changing code by using the properties or methods of the Access or VBA object models.
• Importing or exporting forms, reports, macros, or modules.

  In an MDE database, tables and queries can be imported from or exported to other types of database files. In ADE files, only tables can be imported from or exported to other types of database files.

Note   The process of saving a database as an MDE or ADE file compiles all modules and compacts the destination database, so there is no need to perform these steps before saving a database as an MDE or ADE file.

To save a database as an MDE or ADE file

1. Open the Microsoft Access database (MDB file) or Microsoft Access project (ADP file) you want to work with.

   If the Access database is being shared on a network, first make sure that no other users have it open.

2. On the Tools menu, point to Database Utilities, and then click Make MDE File or Make ADE File.
3. In the Save MDE As or Save ADE As dialog box, enter a file name and the location where you want to save the file, and then click Save.

   The original file is unchanged and a new copy is saved as an MDE or ADE file with the file name and location you specified.

Important   Be sure to keep your original Microsoft Access database (MDB file) or Microsoft project (ADP file) in a safe place. If you need to modify the design of forms, reports, or modules in your file, you must open the original file, modify the objects, and then save the file again as an MDE or ADE file. Also, databases saved as MDE or ADE files in Access 2000 cannot be opened or converted in later versions of Access. To convert or open it in later versions of Access, you must use the original file.

Saving as an MDE or ADE file doesn’t create a run-time version of the file. To use an MDE or ADE file, users must have Access 2000 installed. If you have Microsoft Office 2000 Developer, you can save an MDE file and then use the Packaging and Deployment Wizard to create a Setup program that installs the run-time version of Access and your MDE file.

Top

Modifying MDE and ADE files

If you need to modify the design of forms, reports, or modules in a database saved as an MDE or ADE file, you must open the original MDB or ADP file, modify the items, and then save it as an MDE or ADE file again.

Saving an MDB file that contains tables as an MDE file creates complications when you are reconciling different versions of the data if you need to modify the design of the database later. For this reason, saving a database as an MDE file is most appropriate for the front end of an application that has been split into a front-end/back-end database, in which the back end contains only tables and the front end contains the remaining objects.

An ADP file can only be a front end (client) to server tables, database diagrams, views, and stored procedures. That is, it can contain only connections to these objects, so this isn’t an issue with an ADP file saved as an ADE file.

Top

Using other forms of security with MDE files

Saving a database as an MDE file is a good way to protect the code and the design of forms and reports in a database application, without requiring users to log on and without having to manage the user accounts and permissions required by user-level security. However, an MDE file doesn’t control how users gain access to tables, queries, or macros.

If you want more control over these database objects, establish user-level security for your MDB file before you save it as an MDE file. User-level security settings are preserved in the new MDE file. Alternatively, you can split your MDB file into a front-end/back-end database and establish user-level security for your front-end database to protect access to queries and macros and for your back-end database to protect access to your back-end tables. In this case, you would save only your front-end database as an MDE file to protect the design of forms, reports, and code.

After you have secured your MDB with user-level security, and you want to save it as an MDE file, you must meet the following requirements:

• Before starting Access, you must use the Workgroup Administrator program or the /wrkgrp command-line option to join the workgroup information file (.mdw) that was in use when the database was created.
• Your user account must have Open/Run and Open Exclusive permissions for the database.
• Your user account must have Modify Design or Administer permission for any tables in the database, or you must be the owner of any tables in the database.
• Your user account must have Read Design permission for all objects in the database.

You can also use a database password to control who can open an MDE file, but you must set the password in the original database before you save it as an MDE file. The database password is preserved in the new MDE database.

Top

Saving an MDE file that references another database

If you try to save an MDB or ADP file that references another MDB, ADP, or add-in database (MDA file) as an MDE or ADE file, Access displays an error message and doesn’t let you complete the operation. To save an MDB or ADP file that references another file, you must save all files in the chain of references as MDE or ADE files, starting from the first file referenced.

After you save the first file as an MDE or ADE file, use the References dialog box (Tools menu) to update the reference in the next file to point to the new MDE or ADE file before saving it as an MDE or ADE file, and so on.

For example, if Database1.mdb references Database2.mdb, which references Database3.mda, proceed as follows:

1. Save Database3.mda as Database3.mde.
2. Open Database 2.mdb, and change its reference to point to the new Database3.mde.
3. Save Database2.mdb as Database2.mde.
4. Open Database1.mdb, and change its reference to point to the new Database2.mde.
5. Save Database1.mdb as Database1.mde.

Top

See also

Saving your database as an MDE file prevents all users (including database administrators) from modifying the design of forms, reports, and modules. If you want more control and flexibility in these areas, establish user-level security instead. For more information, see Setting User-Level Security.

To save a replicated database as an MDE file, you must first remove replication system fields, tables, and properties. For more information about making a replicated database a regular database, see Microsoft Access 2000 online Help.