Home > Sample chapters

Securing and Sharing Information in Microsoft Office Access 2007

Securing a Database for Distribution

When a database is used locally, on a local area network (LAN), or on a wide area network (WAN), you have considerable control over who has access to it. But if you send the database out into the world, on its own or as part of a larger application, you lose that control. There is no way you can know who is using the database or what tools they might have available to hack into it. If this is of concern to you, consider distributing your database as an Access Database Executable (ACCDE) file.

Suppose you want to make a database available for use by several organizations in the area, but the organizations don’t want their members to be able to change the database objects and perhaps “break” things. Saving a database as an ACCDE file compiles all modules, removes all editable source code, and compacts the destination database. Users of the ACCDE file can view forms and reports, update information, and run queries, macros, and VBA code. They cannot do the following:

  • View, edit, or create forms, reports, or modules in Design view.

  • Add, delete, or change references to other objects or databases.

  • Modify VBA code.

  • Import or export forms, reports, or modules.

Access can save a database as an ACCDE file only if it is in Access 2007 format. Access 2002 and Access 2003 databases can be saved as MDE files.

You can’t convert a database from ACCDE format to the source ACCDB format, so after saving a database as an ACCDE file, retain the original ACCDB file in a safe place. If you need to make changes to forms, reports, or VBA code, you will have to make them in the original database and then save it as an ACCDE again.

In this exercise, you will secure a database by saving it as a distributable ACCDE file.

  1. On the Database Tools tab, in the Database Tools group, click the Make ACCDE button.

    httpatomoreillycomsourcemspimages1122356.jpg
  2. In the Save As dialog box, navigate to the Documents\Microsoft Press\Access2007SBS\Securing folder, and then click Save.

    The process takes only a moment; no message alerts you when it is completed.

  3. Click the Microsoft Office Button, and then click Open.

    httpatomoreillycomsourcemspimages1121720.jpg

    Microsoft Office Button

  4. In the Open dialog box, navigate to the securing folder.

    Access has created a database executable file named Distribute.accde. The file icon displays a blue lock over the standard Access icon.

  5. Double-click the Distribute database executable file, and if the Microsoft Office Access Security Notice message box appears, click Open.

  6. In the Navigation Pane, right-click one object of each type (table, query, and so on), and note whether the Design View button on the shortcut menu is active.

    The Design View button is available for tables, queries, and macros, but unavailable for all other object types. This prevents you or another user from making any design changes to forms or reports, or changing any VBA code associated with the database.