Advanced Visual Basic Techniques
(Publisher: John Wiley & Sons, Inc.)
Author(s): Rod Stephens
ISBN: 0471188816
Publication Date: 06/01/97

Part Three
Databasics

Some of the programs presented in earlier chapters store data in files. For example, the AppointmentBook application described in Chapter 3 saves appointment information in the file Appts.DAT. Saving data in files is quite convenient for simple programs that do not need to share their data. It is rather cumbersome, however, if the data must be used by more than one application.

Applications that store data in a standardized database can share information much more easily. Using a database also frees the application developer from building customized routines for reading and writing data to files. Chapters 8 through 10 demonstrate database techniques that simplify data management.

The PeopleWatcher application described in Chapter 8 uses data controls and bound controls to allow the user to examine and modify data in a simple personnel database. It also shows how an application can implement privileges and password protection. It uses those privileges to customize its user interface to suit the needs of different users.

The Query application described in Chapter 9 uses data access objects to process commands written in the industry-standard structured query language (SQL). Query takes advantage of the flexibility of SQL to implement a powerful data management tool.

Chapter 10 describes PeopleWatcher Remote. This application extends the PeopleWatcher application described in Chapter 8 across a network. By allowing its database to run on a remote computer, PeopleWatcher Remote gives users from all over a network access to the personnel file.

Chapter 8
PeopleWatcher

PeopleWatcher is a simple company personnel application that allows users to create, view, and modify employee information. In a way, PeopleWatcher is like a glorified phone book. It allows users to look up employees and read information about them.

The next section, “Using PeopleWatcher,” describes PeopleWatcher from the user’s point of view. It explains how PeopleWatcher allows users to create, view, and modify personnel records.

The “Key Techniques” section briefly describes the most important programming methods used to build PeopleWatcher. The rest of the chapter describes these methods in detail.

Using PeopleWatcher

When PeopleWatcher starts, it presents a password dialog similar to the one shown in Figure 8.1. The user enters a user name and password. PeopleWatcher uses these to determine what privileges the user has so it can adjust its user interface accordingly.

Figure 8.2 shows the running PeopleWatcher application. The outline control on the left displays a list of employees grouped by their last initials. The user can click on the plus sign next to a letter to see the entries for employees with last names starting with that letter. In Figure 8.2 the list of employees with last initial S is expanded.

The middle portion of PeopleWatcher shows information about the selected employee. The Basic, Home, HR, and Salary tabs allow the user to view different types of information.

Because the Employee table contains sensitive data, not every user can be allowed to view and modify all of the data fields without violating employee privacy. For example, salary information is confidential so only certain users, such as managers, can view the Salary tab.

The right portion of PeopleWatcher shows a picture of the selected employee.

PeopleWatcher allows certain users to modify some pieces of personnel data, depending on the user’s privileges. For example, a secretary can view and modify an employee’s basic information and can view Home and HR information. A secretary cannot even view Salary data.

FIGURE 8.1  PeopleWatcher’s password dialog.

If an employee has permission to modify a data field, the field is shown with a white background. All of the fields shown in Figure 8.2 are editable so they all have white backgrounds. If the user does not have permission to modify a field, the field’s background is gray. This color coding allows a user to instantly recognize the editable fields.

If the user is not allowed to view a certain type of data, that data is not visible. Figure 8.3 shows Home data viewed by a secretary user. The fields are gray because secretaries are not allowed to modify Home data. The Salary tab is not present because secretaries are not allowed to view Salary information.

FIGURE 8.2  PeopleWatcher.

FIGURE 8.3  Home data viewed by a secretary user.

PeopleWatcher divides employee data into four categories: Basic, Home, HR (human resources), and Salary. Basic information is public and is available to everyone. It includes such items as employee name, phone number, office number, and the employee’s picture.

Home information concerns the employee’s personal life. It includes the employee’s home address, home phone number, and birth date. This sort of information is generally private, though a secretary or manager might need it to contact an employee at home.

HR information includes the employee’s ID number, Social Security number, hire date, and number of hours of unused vacation and sick leave. This data is also private.

Salary information includes the employee’s job level, salary, and pay interval. It is the most sensitive information of all, so it is available to the fewest users.

Table 8.1 lists the permissions granted to each group of users.

To make testing PeopleWatcher easier, the database included on the compact disk defines one user in each group. The users defined in the database and their passwords are listed in Table 8.2.

A user with edit privileges can begin editing either by selecting the Edit command in the Edit menu or by typing directly into an editable field. Because a user cannot type directly into a picture, users who are allowed to modify pictures must do so in a different way. If the user double-clicks on the picture, PeopleWatcher presents a file selection dialog. The user should select the file containing the employee’s new picture.

Once the user has finished making changes, the Edit menu’s Accept Changes command makes the changes permanent. The Cancel Changes command removes the changes and restores the fields to their original values.

Users with appropriate privileges can also create and delete records. For these users, PeopleWatcher’s Edit menu includes the New, Copy, and Delete commands. The New command creates a new blank employee record. The Copy command creates a new record using the same values contained in the current record. The user can change the values before saving the changes to the data. The Delete command asks the user to confirm the deletion before it removes the record.

PeopleWatcher provides two additional commands for managing user passwords. The File menu’s Logon User command presents a password dialog. This allows the user to log on to PeopleWatcher as a different user. This is particularly useful if the user initially enters an incorrect password. In that case, the program will not display any information until the user selects the Logon User command and logs on correctly.

PeopleWatcher’s Change Password command in the File menu presents the dialog shown in Figure 8.4. Here the user should enter a current user name and password, which are required to prevent one user from changing another’s password.