Microsoft Access database applications are normally planned, designed and built using the standard single file format in either .MDB or the newer .ACCDB (Access 2007/2010) and then distributed with all the objects (Tables, Queries, Forms, Reports, Macros and Modules -VBA code) fully accessible with the standard default settings applied.
To help lock down and prevent certain objects especially Forms, Reports and any code being modified (via the design mode), you can convert your database file into an ‘executable‘ file format where the .MDB becomes a copy as an .MDE and the .ACCDB to a .ACCDE.
This will protect and seal the database file which is better than deploying just the open file format into either an Access runtime or the full version of Access still allowing users to be able to utilise, store and process data as well as run the other objects as normal.
It is a quick and easy fix solution knowing that the executable version (which is always a copy of the original) can not be tampered with and re-compiled when new object changes are added.
What are the Benefits of an ‘Executable’ File
The following points highlight the main benefits of using either the .MDE or .ACCDB file format:
- Since the code is compiled and objects are sealed, the overall file size will be reduced and therefore improves the performance.
- Users still benefit from the all the code procedures included with the file conversion file with no compromise in functionality .
- Basic security is applied due to objects being locked and sealed and do not require any Access user-level security (where available).
- Capturing data and running reports is all that users really need to handle as this file format caters exactly for it.
- Objects (forms, reports and vba code modules) cannot be imported or exported as this feature is locked too.
MDE in Access / ACCDE File Requirements
There are some pre-checks prior to converting a file and following (where applicable) should be noted:
- If Access user-level security is in place, then make sure the user creating the file has access rights to do so which would normally be an administrator as they should have access to all objects.
- Make sure all user-level security is correctly set before converting as you will not be able to change profiles later.
- If you have Replication added to your database file (a useful tool to keep versions up to date), this will not be available as the two are not compatible – so remove it first.
- Any external add-ins or references (for any VBA code) needs to be added into the executable file format otherwise they will be unavailable which may affect functionality
How to Convert a Microsoft Access Database to an MDE / ACCDE File
Whichever version of Microsoft Access database you are using, understand that the original file format is never lost (as it is copied) and you use the same open (normal) file format to re-compile the new executable each time.
In Access 2003 (or earlier), the “Database Utilities, Make MDE” command is located under the “Tools” menu. Follow the prompts – it’s that easy.
In Access 2007, Locate the “Database Tools” tab on the Ribbon Bar and under ‘Database Tools’ section there is a “Make ACCDE” icon. Complete the ‘save as’ prompt.
In Access 2010, from the “File” tab which takes you into the ‘Office Backstage’ you will see the tab “Save & Publish” where the option the “Make ACCDE” is one of the ‘save as’ options.
Bonus Tip: You may wish to consider splitting the database into two separate files; one as the ‘back-end’ for your tables only and the other as the ‘front-end’ for all the other objects. This will help to keep your data separate from any potential misuse as the ‘front-end’ is linked to the ‘back-end’ and can be distributed without fear of losing data.
Written By Ben Beitler