Creating Multiuser Access Applications by Linking Tables

A single .mdb file that contains Access application objects (forms, reports, macros, and VBA code modules) and Jet data objects (tables and queries) is one of Access's strongest selling points. Other desktop database management applications such as Visual FoxPro and Visual Basic require multiple files for a single database application. The obvious advantage of a single .mdb file for a complete Access application is simplicity. You can deploy your application by copying its .mdb file to another computer that has Access 2003 installed.

Sharing your Access application with other users in a Windows XP workgroup or Windows 2000/2003 Server domain requires separating the application objects from database objects. It's theoretically possible for multiple users to simultaneously share a single .mdb application on a network or use Windows 2000+ Server's Terminal Services to run multiple instances of the application. In practice, however, application response time and network traffic issues make the single .mdb approach impractical for all but the simplest database projects. Another disadvantage of the single .mdb approach is that making changes to any Access object by opening it in Design mode prevents other users from using the object.

Making your Access application accessible to more than one user at a time requires dividing the application into front-end and back-end components. For Jet databases, the front end contains all application elements plus queries; the back end contains only Jet tables. If you upsize your Access application to the Microsoft SQL Server Desktop Engine (MSDE) or SQL Server 2000, Jet queries become back-end SQL Server views, functions, or stored procedures. In either case, front-end components link to the back-end database objects. The linking process for Jet tables is similar to that for linking dBASE or Paradox files; linking MSDE 2000 tables parallels linking Visual FoxPro files with an Open Database Connectivity (ODBC) driver.

To review the table linking process, see "Linking and Importing External ISAM Tables," p. 272 and "Linking Visual FoxPro Tables with ODBC," p. 274.


Note

Sections later in the chapter cover upsizing Jet tables to MSDE or one of the four SQL Server 2000 editions Personal, Developer, Standard, or Enterprise. This chapter uses SQL Server 2000 to refer to MSDE and any of the SQL Server 2000 editions, except when discussing features that are specific to MSDE 2000.


Multiuser Access applications require that each user have a copy of the front-end .mdb file and network access to the back-end .mdb file or SQL Server 2000. Alternatively, users can run multiple front-end Terminal Server sessions. You can share Jet back ends in a peer-to-peer Windows XP or 2000 Professional workgroup environment or within a Windows 2000/.NET Server domain. Providing network access to MSDE 2000 in a Windows XP or 2000 workgroup environment requires modifying MSDE 2000's security settings. For workgroups, linked Jet back ends are simpler to implement.

Deploying multiuser Access applications requires establishing security and user permissions for front-end application and back-end data objects. You secure and assign permissions for Access front ends and links to back ends with the User-Level Security Wizard and other tools you access from the Tools, Security menu. Front-end security is identical for Jet and SQL Server back ends, so sections near the end of this chapter cover the User-Level Security Wizard and other security tools. Securing back-end .mdb files requires setting individual user or group permissions for the shared .mdb file. For example, you should be the only person who can delete, move, or copy the back-end .mdb file. File-level permissions apply only to Jet back ends, so this very important topic comes early in the chapter.

To learn more about user-level security, see "Applying User-Level Security to Access Front-Ends," p. 783.


Note

The following sections describe creating and securing back-end databases shared by a Windows 2000 Server domain controller or member server. The process for sharing files with Windows NT 4.0 Server or Windows XP Professional workgroup member is similar, but the share and file security settings differ. The examples assume that you're familiar with creating file shares, have an administrative account for the server, and know how to manage Windows XP or 2000 local users and groups.


Creating Linked Jet Tables with the Database Splitter

You use the Database Splitter utility to create a conventional multiuser Access/Jet application from a copy of the application's single .mdb file.

Note

The following example assumes that you haven't applied user-level security settings to the single-file application's Jet tables. Applying user-level security to tables of a single-user application is uncommon. If you've applied these settings, you must copy the System.mdw file for the application to the file share and designate it as the Workgroup Information File for the back-end .mdb file. All users must have Read permission for the System.mdw copy.


To review System.mdw basics, see "Jet Workgroup Information Files," p. 153.


For more information on changing the Workgroup Information File, see "Establishing Your Own Admins Name, Password, and PID," p. 783.


Take the following steps to create and link the back-end database to the front-end application objects:

  1. If the computer on which you're running Access 2003 has a network connection to a server or another workstation, create a share on the server or another workstation in your workgroup to store the back-end .mdb file. The server or other workstation doesn't need to have Office 2003 installed to share the back-end file. Otherwise, add a new folder to store the shared back end .mdb file on your client machine.

  2. graphics/power_tools.gif

    If you're splitting a production database, create and use a copy of the database. This example uses a copy of the Northwind.mdb sample database named NWClient.mdb.

  3. Open the Jet database to split, and choose Tools, Database Utilities, Database Splitter to open the utility's first and only dialog (see Figure 19.1).

    Figure 19.1. The Database Splitter utility has only a single dialog.

    graphics/19fig01.gif

  4. Click the Split Database button to open the Create Back-End Database dialog. The default name of the back-end .mdb is the front-end name with a _be suffix. Change the name to whatever you want; this example uses NWData.mdb as the name.

  5. Navigate to the server share (or local folder, if you're not using a network server). This example uses the Northwind share on the OAKLEAF-MS10 member server of the OAKLEAF Windows 2000 domain (\\OAKLEAF-MS10\Northwind), which also runs SQL Server 2000 (see Figure 19.2).

    Figure 19.2. Specify a share on a workstation or server in your Windows 2000/.NET domain or another workstation in your Windows XP/2000 Professional workgroup.

    graphics/19fig02.gif

    Tip

    Assigning the Uniform Naming Convention (UNC) name //SERVERNAME/ShareName to connect to the server share is a better practice than allocating a local logical drive letter to the share. Users can delete or change the drive letter assignment. You can use Access security to prevent users from changing the UNC link to the back-end file.

  6. Click the Split Button to create the new back-end database and move the tables to it. Click OK to acknowledge the completion message. The links to the back-end tables appear in the front-end application as shown in Figure 19.3.

    Figure 19.3. The table icons of the Database window gain an arrow to indicate that they're linked to the back-end .mdb file.

    graphics/19fig03.gif

  7. Verify that the linked tables are operational by opening each table in sequence and navigating to the last record to assure that the tables are updatable.

  8. Open one of the tables in design view. You receive a message that some properties of linked tables can't be modified in Design view. Click Yes and then click the Properties button to open the Table Properties window. The Description property value defines the link to the back-end .mdb file (see Figure 19.4).

    Figure 19.4. The Description property value of the linked table contains the linking information.

    graphics/19fig04.gif

  9. Run a few queries, and open the forms and reports to verify that the application objects behave as expected with the linked tables.

Establishing Network Share and File Security for the Back End

The first step after you share a back-end database is to set share and file permissions on the .mdb file. Until you remove Full Control permissions of the share and file for ordinary users, your back-end database might be vulnerable to deletion, copying or moving by any network user who has access to the server. The degree of vulnerability depends on permissions assigned to the logical drive or directory in which you create the shared folder. By default, the share and its files inherit permissions assigned to the parent drive or folder. Windows 2000's default share permissions give the Everyone group Full Control; Windows XP and Windows .NET server assign no permissions to the Everyone group. This section's examples assume that the Everyone group has no default or inherited permissions for the share or the back-end .mdb file.

All users of your front-end application need Read and Modify permissions for the share, and Read, Write, Create, and Delete permissions for the folder. These permissions enable the first user to create the .ldb locking file (NWData.ldb for this example) when opening the .mdb file during login and the last user to delete the file when logging off the application. You can assign permissions to groups or users; this example assigns permissions to two users in the oakleaf.org Windows 2000 domain: NWReader1 and NWWriter1.

Note

The locking file contains the computer name and Access security name such as Admin of each person who has an active link to the back-end .mdb file. This file can contain up to 255 records, which is Access's limit for concurrent database users.


Following are the steps required to secure the network share and the NWData.mdb file:

  1. Log on to the server with an account that has local Administrator privileges.

  2. In Explorer, right-click the shared folder (Northwind) and choose properties to open the FolderName Properties dialog. Click the Sharing tab, and then click the Permissions button to open the Permissions for ShareName dialog.

  3. Add the groups or users to the Name list and allow them Read and Change permissions (see Figure 19.5). You need Change permissions for the share to enable all users to create and delete the .ldb file. Click OK to close the Permissions dialog.

    Figure 19.5. You must give Access groups or users Read and Change permissions for the share.

    graphics/19fig05.jpg

  4. Click the Security tab, add the groups or users to the folder Permissions list and allow them Modify permission, which enables all permissions except Full Control (see Figure 19.6). Click OK twice to save your changes and close the Permissions and Security dialogs.

    Figure 19.6. Access users or groups must have Modify permission for the shared folder, even if Write permissions aren't required.

    graphics/19fig06.gif

  5. Right-click the back-end .mdb file and choose Properties to open the FileName.mdb Properties dialog. Clear the Allow Inheritable Permissions from Parent to Propagate to This Object check box to prevent users from inheriting the Modify permission. Clearing the check box displays a Security message. Click Remove to clear all permission check boxes.

  6. Add at least the local Administrators group and grant this group Full Control permission. You might want to grant Full Control privileges to other administrative accounts and groups.

  7. Add groups or users who need read-write access to the tables (NWWriter1 for this example), and grant the accounts Read and Write permissions for the file (see Figure 19.7).

    Figure 19.7. Access data entry users require Read and Write privileges for the back-end database file. Others should be granted Read privilege only.

    graphics/19fig07.gif

  8. Add groups or users who need read-only access to the tables (NWReader1 for this example) and grant the accounts Read permissions for the file.

  9. If you've applied user-level security to the tables copy the System.mdw file for the application to the share, give all users Read permission for System.mdw. Then open the back-end database in Access, and use the Workgroup Administrator to specify the local copy of System.mdw as the workgroup file.

  10. Click OK to apply the file permissions and close the Security dialog.

Tip

You can grant all users read-write permissions for the back-end .mdb file and use Access security to prevent read-only users modifying the file. In this case, Access read-only users who have permission to create new front-ends can gain read-write access to the table. Restricting write permission to authorized users provides an additional layer of security for your back-end database.


Your back-end database files are now secured against inadvertent or intentional deletion or modification by non-administrators.

Verifying Back-End Database Network Security

After you secure the back-end .mdb file, you must assign non-administrative users to the Windows XP or 2000 Power Users group. Members of the default Users group have read-only permissions for Access 2003. It's also a good practice to verify your security settings work by following these steps:

  1. Log on to the computer running the front-end with your administrative account and add the users you created to the local Power Users group.

  2. Log off and log on with the read-only account (NWReader1) and launch Access. The Office 2003 setup program runs to add Registry settings and a workgroup file (System.mdw) for the new user.

  3. Open a table from the front-end application (NWClient) and verify that the tentative append record is missing, which verifies that the back-end database is read-only for this user.

  4. Log off and log on with the read-write account (NWWriter1) and launch Access. The Office 2003 setup program runs again for the new user.

  5. Open a table in the front-end client and verify that the tentative append record is present, which confirms that back-end data is updatable.

  6. Finally, log off and log on with an account that's a member of the Power Users group but doesn't have permissions for the back-end share or file.

  7. Attempt to open a table in the front-end client. You receive a message that the table is locked by another user or you need permission to view the data.

Shared Jet back-end databases are satisfactory for Access multiuser applications that support up to about 25 concurrent users with five or fewer making simultaneous data changes. In this case, you can skip the following sections about SQL Server and implement user-level security for your front-end. If your application involves heavy-duty online transaction processing (OLTP) or the data is vital to the economic survival of your organization, you should consider linking to an SQL Server back end.

Caution

graphics/new.gif

After you split a database, Access 2003's new Tools, Database Utilities, Back Up Database command backs up the front end only. Unless you have automated nightly backup for the server or workstation that shares the back-end data, you must back up the data .mdb file manually. Backing up the .mdb file requires administrative access to the server or workstation.


Tip

As an administrator, you can open the back-end data .mdb in Access and take advantage of Access 2003's database backup and compact/repair utilities. For the preceding example, you would open \\OAKLEAF-MS10\Northwind\NWData.mdb.




Special Edition Using Microsoft Office Access 2003
Special Edition Using Microsoft Office Access 2003
ISBN: 0789729520
EAN: 2147483647
Year: 2005
Pages: 417

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net