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 SplitterYou 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:
Establishing Network Share and File Security for the Back EndThe 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:
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 SecurityAfter 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:
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
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. |