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

The Detacher application in the Ch10\Detacher uses the following code to remove the references created by program Attacher:

Sub Main()
Const LOCAL_DB = “D:\Ch10\PeopleW\Local.MDB”
Const REMOTE_DB = “\\Beauty\C\Ch10\People.MDB”
    On Error GoTo MainError
   
    Set TheDB = DBEngine.Workspaces(0). _
    OpenDatabase(LOCAL_DB)
   
    ‘ Detach the tables.
    DetachTable “Employees”
    DetachTable “UserGroups”
    DetachTable “GroupPrivileges”
    DetachTable “Passwords”

    ‘ Present a success message.
    MsgBox “Tables detached.”
    Exit Sub
   
MainError:
    Beep
    MsgBox “Error” & Str$(Err.Number) & _
      	“ detaching tables.” & vbCrLf & vbCrLf & _
      	Err.Description
    Exit Sub
End Sub

Sub DetachTable(table_name As String)
       TheDB.TableDefs.Delete table_name
End Sub

Using Data Managers

Attaching and detaching remote tables in Visual Basic is fairly easy. It is also easy to attach and detach tables using Visual Basic 4’s Data Manager or Visual Basic 5’s Visual Data Manager.

Attaching with Data Manager The Data Manager command in the Visual Basic development environment’s Add-Ins menu opens the Data Manager. Before attaching tables, the Data Manager must open a local database or create a new one.

The Data Manager’s Attached Tables button opens a list of the database’s remotely attached tables. On the Attached Tables dialog, the New button opens the New Attached Table dialog. After the dialog’s data fields are filled in, clicking the Attach button will make the Data Designer attach to the remote table. Figure 10.1 shows the New Attached Table dialog. In this figure, the remote table Employees in the database \\Beauty\C\People.MDB will be attached to the local table LocalEmployees.

Attaching with Visual Data Manager Visual Basic 5’s Visual Data Manager is similar to the Data Manager provided by Visual Basic 4. The Visual Data Manager command in the development environment’s Add-Ins menu opens the Visual Data Manager. The Visual Data Manager must open a local database or create a new one before it can attach to remote tables.

The Attachments command in the Utility menu displays a list of the database’s remotely attached tables. The New button opens a New Attached Table dialog similar to the one presented by the Visual Basic 4 Data Manager. After the dialog’s data fields are filled in, clicking the Attach button creates the new attachment, just as it does in Data Manager.

Manipulating Remote Tables Once a remote table is attached to a local database, Data Manager and Visual Data Manager can manipulate it just as if it were contained within the local database. For example, double-clicking on the attached table presents a new screen where records can be inserted, edited, and deleted from the remote table.

The local database can even modify the design of the remote table. These changes affect the actual remote table, not just the local view of that table. If other applications remotely attach to the table, changes made by the local database could stop the other applications from working.

FIGURE 10.1  Attaching a remote table using Data Manager.

When the local database removes an attached table, the attachment is removed but the table itself is still present on the remote database. This can create a confusing and potentially dangerous situation. A database designer might delete a table from a database thinking the table is a remote attachment. If it is actually a table stored in the local database, all of the table’s data will be permanently deleted. Designers must be extremely careful when using Data Manager to delete an attachment. Data Manager makes deleting a remote attachment and deleting a local table seem exactly the same, but the consequences are quite different.

Improving Network Performance

Although these techniques allow a program to treat remote tables as if they were present locally, it is important to keep in mind that they are not. Networks are relatively slow. If an application moves large amounts of data to and from a remote database, there may be a noticeable delay. Even when the data accessed is small, there will probably be a delay when the program first connects to the remote database.

PeopleWatcher was not designed specifically for use with remote tables. If the database contains a very large Employees table, the application’s performance may suffer, particularly if the network is heavily used. There are several ways to address this situation.

First, the application can ignore the problem. PeopleWatcher’s largest data transfers occur when the employee list is loaded. This happens when the application starts, when the user deletes an entry, and when the user selects the Refresh list command from the Edit menu. If the database is rarely modified, there is no need for the user to refresh the list. In that case, performance will suffer only when the application is first started. If the user starts PeopleWatcher and leaves it running all day, it will give acceptable performance most of the time. Even the largest transfers should be fairly fast unless the network is very heavily loaded or the database contains several thousand records.

A second option is to redesign the application so it does not give the user access to so much data at one time. For example, the program might require the user to specify a letter of the alphabet. The program would then present a list of only those employees having that last initial. Another strategy would be to allow the user to enter the name or part of the name of the person of interest. The program would then search the database and display only the records that matched.

A more complicated solution is to break large data transfers into a series of smaller ones. PeopleWatcher uses an outline control to list employees. Initially most of the outline’s entries are collapsed. For example, the entry for names starting with “S” is not visible at first. The program could be rewritten so that it loads data only as it is needed. When the user expands the “S” entry in the outline control, PeopleWatcher would fetch the records for employees with the last initial S. This practice of fetching data only as it is needed would provide good performance, but it would make the application a bit more complicated.

Summary

An application can use remote databases either by directly accessing to the remote database, or by attaching the remote table to a local database. The latter method generally performs better. Both methods are extremely easy, so extending applications such as PeopleWatcher across a network is simple.