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

Converting a Microsoft Access Database to an MDE File:

The situation may arise when you quickly need to secure and distribute a Microsoft Access database that also contains Microsoft Access VBA code. You will protect the database from editing by restricting the database users access to the design views, however you will need to allow the users to process their data as normal.

It may be that you haven't got the time to fully create Microsoft Access User Accounts or to set Microsoft Access Security Permissions. By knowing what can and cannot be done by using the Microsoft Access MDE file format, will allow you to make a decision and to quickly release a secure database to the users.

Microsoft Access MDE File Format

The MDE file format is a special extension of the standard Microsoft Access MDB format, and is used to distribute an application. An MDE file compiles all of the modules, removes all editable source code and compacts the database. The resulting MDE file allows the database users to perform normal database operations, however it prohibits any changes from being made to the objects that support, or the code that runs the application.

Figure1: Creating an MDE File

Benefits of an MDE File

There are various benefits to be had by creating and distributing the database as an MDE file:

• Reducing the size of the database - the size of the database will be reduced due to the removal of the code, so memory usage is optimized, which will improve performance.
• Allowing VBA code to run, but protection from the code being viewed or edited.
• Security of objects without requiring additional Microsoft Access user-level security to be set.
• Allowing database users to update data and run reporting options without having to provide the full database.

Forms and Reports in an MDE File

By converting your Microsoft Access database into an MDE file you will prevent your database users from tampering with your VBA code. When a user accesses an MDE file, they cannot:

• View, modify or create Microsoft Access forms in design view.
• View, modify or create Microsoft Access reports in design view.
• View, modify or create Microsoft Access modules in design view.
• Add, delete or change references to databases or to object libraries.
• Change any VBA code - there is no source code available.
• Import or export any forms, reports or code modules.

MDE File Requirements

There are some requirements to saving your Microsoft Access MDB file as a Microsoft Access MDE file, and the following conditions apply:

• You must have password access to the Visual Basic code in the database.
• You must save all references to other Microsoft Access databases.
• You must have removed replication from a replicated database.
• If you want to remove user-level security options, you must do so prior to converting the file to the MDE format.
• If your database does have user-level security and you do need to preserve this, you must do:
  • Be a member of the workgroup that defines the user accounts used to access the database, or that was in use when the database was created.
  • Have Open/Run and Open Exclusive permissions for the database.
  • Have Modify Design or Administer permissions for any tables in the database, or be the owner of any of the tables in the database.
  • Have Read Design permissions for all objects in the database.

How to Convert a Microsoft Access Database to an MDE File

Once you have determined that you meet the above criteria for converting an Access database in MDB format to the MDE format, it is a simple process to make the conversion happen. You will need to perform the following actions:

1. If necessary, convert the database into the latest Microsoft Access version - if the file format is Access 2000 and you are working in Microsoft Access 2003 you will need to convert the database file format to Access 2003:
   1. From the Tools menu, choose Database Utilities » Convert Database » To Access 2003 File Format.
   2. Navigate to the database that needs to be converted and click OK.
   3. Enter a name for the database and click Save.
   4. Click OK to dismiss the message box.
   5. Open the database to confirm the name change.
2. Choose Tools » Database Utilities » Make MDE File.
3. Select the database to be converted and click Make MDE.
4. Confirm that the file has been converted to MDE and click Save.
5. Open the new file, and confirm that the Code toolbar is disabled and that the application works as expected.