Access 2003 offers an additional level of security through the creation of MDE files. An MDE (compiled database) file is a database file that has all editable source code removed. This means that Access eliminates all the source code behind the forms, reports, and modules contained in the database. An MDE file offers additional security because the forms, reports, and modules in an MDE file cannot be modified. Other benefits of an MDE file include reduced size and optimized memory usage. To create an MDE file, follow these steps:
Open the database on which you will base the MDE file.
Choose Tools | Database Utilities | Make MDE File. The Save MDE As dialog box appears.
Select a name for the MDE file and click OK.
Before you begin creating and using MDE files, you need to be aware of the restrictions they impose. If you plan ahead, these restrictions probably will not cause you too many problems. On the other hand, if you enter the world of MDE files unaware, they can cause you much grief. You should consider these restrictions:
No one can view or modify the design of the forms, reports, and modules in an MDE file. In fact, no one can add new forms, reports, and modules to an MDE file. It is therefore important that you keep the original database when you create an MDE file: That is where you will make changes to existing forms, reports, and modules and add new forms, reports, and modules. When you are finished making changes to the database, you simply rebuild the MDE file.
Because you must rebuild the MDE file every time you make changes to the application, the front-end/back-end approach is a good approach to take: You place the tables in a standard Access database and store the other objects in the MDE file. You can therefore rebuild the MDE file without worrying about the reconciliation of data.
You cannot import or export forms, reports, or modules to or from an MDE file.
You cannot convert an MDE file to later versions of Access. It is necessary to convert the original database and then rebuild the MDE file with the new version.
You cannot add or remove references to object libraries and databases from an MDE file. Also, you cannot change references to object libraries and databases.
Every library database that an MDE references also must be an MDE file. This means that if Database1 references Database2, which references Database3, all three databases must be stored as MDE files. You first must save Database3 as an MDE file, reference it from Database2, and then save Database2 as an MDE file. You can then reference Database2 from Database1, and finally, you can save Database1 as an MDE file.
A replicated database cannot be saved as an MDE file. The replication must first be removed from the database. You accomplish this by removing the replication system tables and properties from the database. The database can then be saved as an MDE file, and the MDE file can be replicated and distributed as a replica set. Any time changes must be made to the database, they must be made to the original database, resaved as an MDE file, and then redistributed as a new replica set.
Any security that applies to a database follows through to an MDE file that is created from it. To create an MDE file from a database that is already secured, you must first join the workgroup information file associated with the database. You must have Open/Run and Open Exclusive permissions to the database. You must also have Modify Design and Administer permissions to all tables in the database, or you must own all tables in the database. Finally, you must have Read Design permissions on all objects contained in the database. These permissions are all covered in Hour 22, "Security Introduced."
If you want to remove security from the database, you must remove the security from the original database and rebuild the MDE file.
As long as you are aware of the restrictions associated with MDE files, they can offer many benefits. In addition to the natural security they provide, the size and performance benefits MDE files offer are significant.
A great use for MDE files is for demo versions of applications. Performance of MDE files is excellent, but more importantly, if you use Visual Basic for Applications (VBA) code, MDE files can easily be rendered both time- and data-limited.
Other programmatic limitations exist regarding MDE files that are beyond the scope of this book. To learn more about these restrictions, please consult a programming text such as Alison Balter's Mastering Access 2003 Desktop Development (Sams Publishing).