Securing and Sharing Information in Microsoft Office Access 2007

1/3/2007

Contents

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.

Splitting a Database for Distribution

To help database performance and reliability in a shared implementation, such as when multiple people will access a database over a network at the same time, you can split the database into two parts: A back-end database containing the tables and a front-end database containing the forms, queries, and reports. To split a database:

Make a copy of the database on your computer, and then open it.
On the Database Tools tab, in the Move Data group, click the Access Database button. Then in the Database Splitter wizard, click Split Database.
In the Create Back-end Database dialog box, specify a name and storage location for the back-end database, click Split, and then click OK in the message box telling you that the split was successful.

Distribute the front-end fifile (the one you started with) to the database users. It will automatically connect to the back-end fifi le (stored in the location you specififi ed in step 3).

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

On the Database Tools tab, in the Database Tools group, click the Make ACCDE button.
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.
Click the Microsoft Office Button, and then click Open.

Microsoft Office Button
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.

TIP

The Distribute.laccdb file indicates that your database is locked, because it is currently open. When you close the database, the .laccdb file will disappear.
Double-click the Distribute database executable file, and if the Microsoft Office Access Security Notice message box appears, click Open.
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.

IMPORTANT

When creating an ACCDE file from a database with multiple users, first make sure that all other users close the database. You will know that someone else has the database open if you locate it in Windows Explorer and see a file of the same name, with an .laccdb (locked Access database) extension. If you open the database anyway, when you attempt to create the accde file you will be warned that the database is already opened by someone else (the username and machine name are provided) and told to try again later.

Collaborating Through SharePoint

If your organization has a Microsoft Office SharePoint Server 2007 collaboration site, you can manage data collection and distribution through that site, by making your Access database available online, in one of these ways:

Migrate a database to a collaboration site. The Move To SharePoint Site wizard creates SharePoint lists linked to your database. The linked data can be accessed and managed from the collaboration site or from Access.
Publish a database to a collaboration site. You can publish your database to a SharePoint library, and provide forms, queries, and reports through which other people can update or extract information.

After you make the database available to collaboration site users, they can work with the information as they would with any other SharePoint list content—online or offline—but they can also view the content as it would appear in Access, without first starting the program.

One benefit of sharing a database in this way is that permissions are regulated by SharePoint rather than set for the individual database. Another is that you can track changes made to the data by site users and recover previous versions of information, thus safeguarding against lost data in a way that you can’t with Access alone.

Conversely, you can create a database from existing SharePoint lists, and integrate workflow processes with a database.

Save to your account