• Choose a category
• All
• Classic-Fiction

By Root 2000 0

users who may be spread across a large network. This chapter explores some solutions to common problems in multiuser applications. You'll learn how to use a shared database table to help your users communicate with one another and see how to find out which users are logged in at any given time. You'll also learn how to implement basic transaction logging, how to determine who has a record locked, and how to prevent a user from locking a record for an excessive time period. Because multiuser applications often use Access security, we also explore the security system in detail. For instance, you'll learn how to properly secure your database, how to keep track of your users and groups, and how to check if they have blank passwords. You'll also see how you can maintain separate but synchronized copies of a database using Access replication.

WARNING

Several of the examples in this chapter take advantage of the DAO type library, rather than the default ADO library used by Access 2002 and Access 2003. Even though it's less "modern," DAO provides greater functionality, and generally better performance. In addition, using DAO makes it possible for these demonstrations to work in earlier versions of Access. If you want to try these techniques in your own applications, make sure you add the DAO reference to your project using the Tools → References menu item from within VBA—it won't be added by default.

10.1. Properly Secure Your Database

Problem

The database you've developed contains sensitive data to which you wish to limit access. You'd like to be able to create different classes of users so that some users have no access to this data, others can read the data but can't change it, and still others can modify the data. At the same time, you don't want to secure every object in the database this way; you'd like to apply security only to selected objects. Is this possible with Access?

Solution

Access supports two forms of security: workgroup-based security and database-password security. If you use the simpler database-password security system, you can assign only a single password to the entire database, which is inadequate for your purposes. Fortunately, your needs can be met by using the more sophisticated workgroup-based security system. However, securing a database this way can be tricky. This solution guides you through the process, starting with a completely unsecured database and finishing with a well-secured database that should meet your needs.

Before you can properly secure your database, you must have a security plan. Consider who will be using the database and what security permissions those users should have for each database object. With a plan in place, you can go about securing your database.

Make a security plan

The first step in creating a security plan is to make a list of the people who will be using the database. Write out the names of the users and put them into distinct groups. A user can be a member of more than one group, but you need to assign each user a unique name. Users will have to type in their usernames each time they log into Access, so you may wish to keep the names as short as possible (but still unique). In a small workgroup, you may be able to use an individual's first name; in larger settings, you may need to use the first name plus the first letter of the last name or some similar scheme to ensure uniqueness. For example, if you were charged with designing a secured database for the solution company, you might come up with the users and groups in Table 10-1.

Table 10-1. The plan of users and groups

Group

Members

Employees

Tom, Pat, Bill

Programmers

Paul, Peter

Managers

Joan, Thomas, Paul

Admins

Paul

There are several things to note in Table 10-1. First, Paul is both a manager and a programmer. Second, two individuals in this company are named Tom but, to ensure uniqueness, we've assigned one of the Toms the username Thomas. Third, we recommend using the following convention: make usernames singular and group names plural. Finally, you need to