Microsoft Office 2000/Visual Basic Programmer's Guide

Saving Your Solution Without VBA Source Code

If your primary security concern is protecting your VBA code and the design of your forms and reports, you can save your .mdb as an MDE (.mde) file, or your .adp as an ADE (.ade) file. An .mde file is an Access database file with all modules compiled and all editable source code removed. Likewise, an .ade file is an Access project file with all modules compiled and all editable source code removed. You can also save a database as an .mde or .ade file in conjunction with other forms of security.

Saving your database as an .mde or .ade file creates a separate copy of your database that contains no VBA source code and that is smaller than the original database. Your code is compiled and continues to run, but it can't be viewed or edited. Additionally, users can't view or modify the design of forms, reports, and modules in an .mde or .ade file. Users can still view and modify the design of your database's relationships, tables, queries, and macros after you have saved the database as an .mde file; however, you can establish User-Level security if you want to protect the design of these objects. Similarly, saving an .adp file as an .ade file doesn't protect the design of database diagrams, tables, views, stored procedures, and macros, but you can establish security on the server itself to protect all of these objects except macros.

Saving your database as an .mde or .ade file also prevents the following actions:

• Creating forms, reports, or modules.
  
  
• Adding or deleting references to object libraries or databases by using the References command on the Tools menu.
  
  
• Using the Object Browser.
  
  
• Changing code by using the properties or methods of the Access or VBA object models (because .mde and .ade files contain no source code).
  
  
• For .mde files, importing or exporting forms, reports, macros, or modules. However, tables and queries can be imported from or exported to non-.mde databases. Any tables or queries in an .mde database can be imported into another database, but no forms, reports, macros, or modules can be imported into another database. For .ade files, only tables can be imported from or exported to non-.ade databases

If you need to modify the design of forms, reports, or modules in a database saved as an .mde or .ade file, you must open the original .mdb or .adp file, modify the items, and then save it as an .mde or .ade file again. Saving an .mdb that contains tables as an .mde file creates complications when you are reconciling different versions of the data if you need to modify the design of the solution later. For this reason, saving a database as an .mde file is most appropriate for the front end of an solution that has been split into a front-end/back-end database, in which the back end contains only tables and the front end contains the remaining objects. An .adp can only be a front-end (client) to server tables, database diagrams, views, and stored procedures. That is, it can only contain connections to these objects, so this isn't an issue with an .adp file saved as an .ade file. For more information about splitting databases, see "The Two-Database Approach" in Chapter 16, "Multiuser Database Solutions."

Note   The process of saving a database as an .mde or .ade file compiles all modules and compacts the destination database, so there is no need to perform these steps before saving a database as an .mde or .ade file.

To save a database as an .mde or .ade file

1. Open the Access database (.mdb) or project (.adp) file you want to work with. If the Access database (.mdb) is being shared on a network, make sure no other users have it open.
   
   
2. On the Tools menu, point to Database Utilities, and then click Make MDE File or Make ADE File.
   
   
3. In the Save MDE As or Save ADE As dialog box, specify a file name and the location where you want to save the file, and then click Save.

   Your original file is unchanged and a new copy is saved as an .mde or .ade file by using the file name and location you specified.

   Caution   Be sure to keep your original Access database (.mdb) or project file (.adp) in a safe place. If you need to modify the design of forms, reports, or modules in your file, you must open the original file, modify the objects, and then save the file again as an .mde or .ade file. Also, databases saved as .mde or .ade files in Access 2000 cannot be opened or converted in later versions of Access. To convert or open it in later versions of Access, you must use the original file.

Note   Saving as an .mde or .ade file doesn't create a run-time version of the file. To use an .mde or .ade file, users must have Access 2000 installed. Alternatively, if you have Microsoft Office 2000 Developer, you can save an .mde or .ade file, and then use the Package and Deployment Wizard to create a setup program that installs the run-time version of Access and your .mde or .ade file.

Using Other Forms of Security with an Access Solution That Will Be Saved as an .Mde or .Ade File

Saving an .mdb file as an .mde file is a good way to protect the code and the design of forms and reports in your solution, and it doesn't require users to log on or require you to create and manage the user accounts and permissions required by User-Level security. However, an .mde file doesn't control how users access your solution's tables, queries, and macros. If you want more control over these database objects, you should establish User-Level security before you save your database as an .mde file. User-Level security will be preserved in the new .mde file. Alternatively, you can split your .mdb into a front-end/back-end database and establish User-Level security for your front-end database to protect access to queries and macros, and also for your back-end database to protect access to your back-end tables. In this case, you would save only your front-end database as an .mde file to protect the design of forms, reports, and code.

However, saving your database as an .mde file prevents all users (including database administrators) from modifying the design of forms, reports, and modules. If this is too restrictive and you require additional control and flexibility in these areas, you shouldn't save your database as an .mde file — you should establish User-Level security instead.

Once you have secured your .mdb with User-Level security, if you want to save it as an .mde file, you must meet the following requirements:

• Before starting Access, you must use the Workgroup Administrator program (Wrkgadm.exe, installed by default in the C:\Program Files\Microsoft Office\Office\1033 subfolder) or the /wrkgrp command-line option to join the workgroup information file (.mdw) that was in use when the database was created.

Note   The path to Wrkgadm.exe reflects the language ID folder (1033) for U.S. English language support in Office. The language ID folder below C:\Program Files\Microsoft Office\Office differs for each language.

• Your user account must have Open/Run and Open Exclusive permissions for the database itself.
  
  
• Your user account must have Modify Design or Administer permission for any tables in the database, or you must be the owner of any tables in the database.
  
  
• Your user account must have Read Design permission for all objects in the database.

You can also use a database password to control who can open an .mde file, but you must set the password in the original database before you save it as an .mde file. The database password will be preserved in the new .mde database.

Similarly, saving an .adp as an .ade file doesn't control how users access the tables, views, stored procedures, and database diagrams in the SQL Server database that your .ade file is connected to. To protect access there, you must establish security for those objects on your server by using the SQL Server Enterprise Manager, the properties and methods of the Group and User objects in ADO code, the GRANT and REVOKE statements in Transact-SQL, or security management system stored procedures such as sp_addrole and sp_addrolemember.

Saving a Replicated Database as an .Mde File

A replicated database (either a replica or Design Master) cannot be saved as an .mde file; however, once a database is saved as an .mde file, it can be replicated. To save a replicated database as an .mde file, you must first remove replication system fields, tables, and properties. For more information, search the Microsoft Access Help index for "replicated databases, making regular." For more information about database replication, see Chapter 16, "Multiuser Database Solutions."

Saving .Mdb or .Adp Files That Reference Other Files as .Mde or .Ade Files

If you try to save an .mdb or .adp file that references another .mdb, .adp, or add-in database (.mda) as an .mde or .ade file, Access displays an error message and doesn't let you complete the operation. To save an .mdb or .adp file that references another file, you must save all files in the chain of references as .mde or .ade files, starting from the first file referenced. After saving the first file as an .mde or .ade file, you must then use the References dialog box (Tools menu, References command) to update the reference in the next file to point to the new .mde or .ade file before saving it as an .mde or .ade file, and so on.

For example, if Database1.mdb references Database2.mdb, which references Database3.mda, you would proceed as follows:

1. Save Database3.mda as Database3.mde.
   
   
2. Open Database 2.mdb and change its reference to point to the new Database3.mde.
   
   
3. Save Database2.mdb as Database2.mde.
   
   
4. Open Database1.mdb and change its reference to point to the new Database2.mde.
   
   
5. Save Database1.mdb as Database1.mde.