If you make an Access 2003 database for other people — especially people who may be a teeny bit clueless about Access — you may want to lock up your database to prevent other users from making changes that may break it. You can add security in the form of user names and passwords, but a simpler option is to turn your database from an MDB file to an Mde File.
An MDE file is the same as a regular Access MDB database file, with the following changes:
If the MDE file is a front-end file, with no data stored in it, you can just make your changes to the original MDB file and resave it as an MDE file. Because all your data lives in the back-end database, you're all set.
However, if your MDE file contains tables full of valuable information, you can't just abandon it. If you use the MDE file to do data entry and editing, that file contains your up-to-date tables. The original MDB file has editable forms, reports, and VBA code, but doesn't have the latest version of the data stored in your tables. Not a problem.
Follow these steps to update your MDE file:
An MDE file is the same as a regular Access MDB database file, with the following changes:
- All VBA procedures are compiled — converted from human-readable code (more or less readable, anyway) to a format that only the computer understands. This change prevents a database user from reading or changing your VBA code.
- No one can create forms or reports or modify the existing ones (you can't even open them in Design view). You can't import any, either.
Be sure to keep a copy of your original MDB file! If you need to make changes to your VBA code, forms, or reports (or create new ones), you need to use the MDB file, not the MDE file. MDE files are most commonly used for the front-end database when you split an application into two databases (front end and back end).
Creating an MDE file
Saving your MDB file as an MDE file is easy. Follow these steps:1. Make sure your database is in Access 2002/2003 file format by opening the database.
Take a look at the title bar of the Database window. (Press F11 if it's not visible.) If the title bar says "(Access 2000 file format)" then you need to convert it to the latest file format.
2. Choose Tools --> Database Utilities --> Make MDE File.
Access closes the database to do the conversion. Then you see the Save MDE As dialog box.
3. Specify the folder and file name for the file and click the Save button.
Access creates the new MDE file while leaving the original MDB file untouched. Then the new MDE file opens.
If Access runs into a problem while making the MDE file, a message appears with a Show Help button. Click the button to find out what's wrong.Making updates later
Sooner or later, you are going to want to make a new report or fix an annoying typo in a form. You have to go back to your MDB file to make these kinds of changes, because you can't make changes in an MDE file.If the MDE file is a front-end file, with no data stored in it, you can just make your changes to the original MDB file and resave it as an MDE file. Because all your data lives in the back-end database, you're all set.
However, if your MDE file contains tables full of valuable information, you can't just abandon it. If you use the MDE file to do data entry and editing, that file contains your up-to-date tables. The original MDB file has editable forms, reports, and VBA code, but doesn't have the latest version of the data stored in your tables. Not a problem.
Follow these steps to update your MDE file:
1. Rename your MDE file as a backup file.
For example, add today's date to the end of the file name (right before the .mde part). You're about to create a new MDE file, but you don't want to lose the data in this file.
2. Open the original MDB file and make any changes to contain the forms, reports, and VBA code that you want.
If you plan to make drastic changes, make a backup copy of the MDB first.
3. Choose Tools --> Database Utilities --> Make MDE File and save it as an MDE file with the name that your MDE file originally had.
Now you have an updated MDE file with new, improved forms, reports, and VBA procedures, but with old data. You also have an updated MDB file with your new, improved forms, reports, and VBA code (but out-of-date tables).
4. Delete all the tables from this new MDE file.
In the Database window, click the Tables button in the Objects list, click each table in the list that appears in the right pane and then press the Delete key for each table. You need to confirm each deletion by clicking the Yes button. Deleting tables sounds dangerous, but remember you have all these tables stored safely in your old MDE file.
5. Import the tables from the old MDE file to the new one.
Choose File --> Get External Data --> Import and choose the name you gave your old MDE file in Step 1.
You see the Import Objects dialog box, with tabs for Tables, Queries, Forms, Reports, and other objects.
6. Click the Select All button with the Tables tab selected and then click OK.
Access imports your tables from the original MDE to the new MDE files, replacing the older data in the tables.
7. Import any queries or macros in the old MDE database that you created or changed.
Repeat Steps 5 and 6, but use the Queries and Macros tabs on the Import Objects dialog box to import whatever has changed.
If you are going to do this often, consider splitting your table into a front end and a back end. With a split database, you don't have to re-import your updated tables: You can just leave them in the unchanged back-end database.
