Creating MDE, ADE, and ACCDE Files
"MDE…!? What's an MDE?"
Simply put, an MDE is a compiled MDB with all editable source code removed. Even though this article deals primarily with MDB/MDE conversion, the same principles apply to Access Data Projects (ADP/ADE) and Access 2007 File Format (ACCDB/ACCDE).
Why would you want to compile an .MDB?
Probably the most popular reason is to protect your VBA code. After all, you worked very hard at creating it. Why not protect it? When an MDB is compiled, all of the VBA code behind any form, report, or in a module becomes inaccessible, unchangeable. Not only the code itself, but even the design of forms and reports becomes unchangeable. This can be a very good thing, especially if you want to closely control all modifications to your database or if you are marketing it to the public.
Another benefit that you get from making an MDE is that your application will be smaller, consuming less memory, and will therefore be more efficient. Maybe not by huge leaps and bounds faster, but the performance will improve.
Are there any drawbacks to creating an MDE?
In order to create an MDE, you code has to be 100% syntactically correct. What does that mean? It means you'll need to clean up after yourself if you've left any code "hanging around" where it shouldn't be during development. We'll talk about how to find these types of errors later.
Error Handling (Global) has to be employed through the entire application. If an error occurs, you can no longer be taken to Debug mode in VBA to fix it. Therefore, thorough testing should be done to provide the user with customized messages to specifically identify the problem.
You also lose the ability to programmatically switch to design view of a form or report, modify it, and switch back. While most applications have no need to do this, if you employ this technique you will have to find an alternative method to accomplish your goals.
As a developer, all of the above things are usually considered a good practice to get into regardless of whether you're making an MDE or not.
How do I create an MDE file?
I cannot create an MDE file from my MDB.
The 'Make MDE File…' option is grayed out.
When I choose the 'Make MDE File…' option, nothing happens or an error occurs.
Access can only create an MDE file when the database version is the same as the version of Access you are using. (Ex. Access 2003 cannot create an MDE when the database format is 2000) Convert the .MDB into your version of Access and create the MDE.
Let's take all scenario's starting with ACC97.
MDE creation can only occur when the version of the Access is the same as the file format of the MDB. *
* Access 2007 has two native formats (ACC2002/2003 and ACC2007) so it can create MDE's for both.
** Access 2007 ACCDB files use ACCDE for the compiled extension.
Another reason that MDE creation may fail is because there are problems with your code. Since Access has to compile all the code, there cannot be any errors.
After a successful Compile, this option will appear grayed out on the menu.
TIP: Use Option Explicit! Make sure at the top of EVERY module these two lines of code appear:
Option Compare Database
Without going in to a lot of detail, these lines will make it much easier for you to find errors with your code. Take the time to set this as the default, by going to the VBA window (ALT-11)
This will add these lines to any new form/report/module that you create. You will be a better programmer for it.
Things to remember
Creating an MDE file from your database prevents anyone from getting to the VBA code or form/report design … including you. The only way to make design changes is with the original MDB file.
This makes it EXTEREMELY IMPORTANT to keep your original MDB's in a safe place. Back them up often and store them in multiple locations. They are your only means of doing any future development to your applications. MDE's cannot be "upgraded" or converted to other versions of Access. You still need the original MDB to convert to the desired version, and then to convert the resulting MDB to the MDE format.
Converting to an MDE has no effect on tables, relationships, queries, or macros. These items can still be created/modified/deleted after the conversion.
If your VBA source code is password-protected, you need to have access to the code via the password in order to create the MDE.
If your database is protected by ULS (User-Level Security), conversion to an MDE is best performed while logged on with full administrative rights to the database. You cannot remove ULS from an MDE. Remove ULS from the MDB and then create the MDE.
If you are converting a replicated database, replication must be removed prior to converting to an MDE.
Recently, a company [that will not be mentioned here] advertises the ability to uncompile MDE's back into MDB's (for a price) when proof of ownership is provided. Some may have mixed emotions about this, but thought it would be best if developers were made aware of this possibility.
So give it a try. You've got nothing to lose. Your original MDB is not altered at all during this process. It is one of the best ways to create moderately secure Access applications.
|This page has been accessed 37,214 times. This page was last modified 18:30, 28 February 2013 by Mark Davis. Contributions by Jack Leach and Walter Niesz Disclaimers|