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

Chapter 10
PeopleWatcher Remote

Chapter 8 describes PeopleWatcher, a computerized personnel system that provides different views of employee data to different users based on the permissions they have. This and many other database applications can be moved onto a network with surprisingly little effort.

PeopleWatcher Remote is very similar to PeopleWatcher. The only difference between the two is in how they access the personnel data. The PeopleWatcher Remote’s user interface is identical to the previous version, so this chapter does not describe the application from the user’s point of view. To read a description of PeopleWatcher’s user interface, see “Using PeopleWatcher” in Chapter 8.

The “Key Techniques” section that follows lists the Visual Basic programming techniques described in this chapter. Later sections describe those techniques in detail.

Key Techniques

An application can access a remote database in a couple of ways. The two most common methods are to directly access the remote database and to attach the remote tables to a local database. The following sections describe these two methods.

•  Accessing Remote Databases Directly. One method for accessing remote data is to attach directly to the remote database. This section explains how a data control can make this attachment.

•  Attaching Remote Tables. A second way to access remote data is to attach remote database tables to a local database. This section explains how a program can attach and detach remote tables.

•  Improving Network Performance. Applications that transfer large amounts of data across a network often pay a price in reduced performance. This section describes several ways PeopleWatcher could be redesigned to improve its performance over a busy network.

The Ch10 directory on the compact disk contains several subdirectories that demonstrate these techniques. The project Direct.VBP in the Ch10\Direct directory uses a data control to access a remote database directly.

The projects Ch10\Attacher\Attacher.VBP and Ch10\Detacher\Detacher.VBP show how to attach and detach remote database tables to a local database. The project Ch10\PeopleW\PeopleW.VBP uses the local database to provide access to the remote tables.

Accessing Remote Databases Directly

To access a remote database directly, a program should specify the database’s remote connection information when opening it. For instance, suppose the remote computer Beauty contains an Access database stored in the file C:\Ch10\People.MDB. If the network is configured so a program can access this file system from the local computer, the program can use the following code to open the remote database.

Dim db As Database

    Set db = DBEngine.Workspaces(0).OpenDatabase _
	(“//Beauty/C/Ch10/People.MDB”, False, False, “”)

Once the program has opened the remote database, it can work with it as if it were local. It can create, open, and delete tables, define QueryDefs, create Recordsets, and so forth.

Using Remote Databases with Data Controls

A program can also connect a data control to a remote database. The control’s DatabaseName property should specify the remote connection information. To bind a data control to the database used in the previous example, the control’s DatabaseName property should be set to //Beauty/C/Ch10/People.MDB.

The control’s RecordSource property should be set just as it would be for a local database. The RecordSource can be set to the name of a table in the remote database or to an SQL statement.

Text boxes and other controls can be bound to the data control as usual by setting their DataSource properties to the name of the data control. The DataField property for these controls should be set to the name of a data field selected by the data control.

The example program Direct in the compact disk’s Ch10\Direct subdirectory uses this technique to manipulate records in the database stored in //Beauty/C/Ch10/People.MDB. To modify this application to run on your network you must do two things:

1.  Copy the database file People.MDB from the Ch10\Direct subdirectory to a remote machine. Place the files in a directory you can access from your computer.

2.  In the Direct application, change the EmployeeData control’s DatabaseName property to indicate the new location for the database file.

If your computer does not have access to another machine’s file system, you cannot really test this program. You can change the data control’s DatabaseName property to indicate the location of the database on your own computer, but then you will not be testing Visual Basic’s network capabilities.