Microsoft Office 2000/Visual Basic Programmer's Guide

Securing a Front-end/Back-end Solution

There are additional factors to take into account when establishing User-Level security for a solution that has been split into a back-end database that contains only tables, and a front-end database that contains the remaining objects plus links to the tables in the back-end database. You may not know the name of the specific network location for the back-end database in advance, or you may want to allow a database administrator to move the back-end database. In either situation, users must be able to relink the tables in the back-end database.

To establish User-Level security for a front-end/back-end solution

1. For both databases, follow the procedure described earlier in this section for establishing security with the User-Level Security Wizard.
   
   
2. Assign users to the appropriate groups so that they have permissions to read, update, insert, or delete data in tables in the back-end database, or remove all permissions for those tables and create queries in the front-end database that have the RunPermissions property set to Owner's, and that use the tables in the back-end database. All users must be assigned to groups that have Open/Run permission for the Database object in the back-end database. This is true for all predefined groups created with the User-Level Security Wizard. This is required even if you are using queries with the RunPermissions property set to Owner's.

   For more information about the RunPermissions property, see "Using the Access RunPermissions Property with User-Level Security" later in this chapter.

3. In the front-end database, grant users Modify Design permission for the table links. (Doing so does not grant them the same rights to the tables in the back-end database.)
   
   
4. When the users first install your solution, have them run the Linked Table Manager (Tools menu, Add-ins submenu) from the front-end database to refresh the links to the tables in the back-end database in its new location.

   Tip   You can also write code that prompts users during startup to refresh table links. For sample code that refreshes table links, see "Refreshing Links for Linked Tables" in Chapter 14, "Working with the Data Access Components of an Office Solution."

Because users have Modify Design permission for the linked tables in the front-end database, they can reset the links to the back-end tables if the location of the back-end database changes. They can't make any modifications to the design of the actual tables in the back-end database.