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

Attaching Remote Tables

The second method for accessing remote data is to attach the remote tables to a local database. When the remote tables are attached, a local name for them must be specified. This is the name the tables will be known by in the local database. Using this name, an application can access the tables as if they were local. Generally, accessing remote tables by attaching them to a local database is faster than accessing them directly, as described in the previous section.

Remote tables can be attached and detached either programmatically or by using the Data Manager. The following sections describe these two methods.

Attaching Programmatically

Normally to build a new table in a database using Visual Basic a program follows these steps:

1.  Use the database object’s CreateTableDef method to create a new TableDef object.

2.  Use the TableDef object’s CreateField method to create and define Field objects to represent the fields the table should hold.

3.  Append the new Field objects to the TableDef object’s Fields collection.

4.  Append the new TableDef object to the database’s TableDefs collection.

The following code fragment adds a new table called NewTable to the People.MDB database. The table contains one integer field named IntField and one date field named DateField.

Dim db As Database
Dim td As TableDef
Dim new_field As Field

   ‘ Open the database.
   Set db = Workspaces(0).OpenDatabase(“People.MDB”)

   ‘ Create a new TableDef.
   Set td = db.CreateTableDef(“NewTable”)

   ‘ Create and define the fields and add them to the table.
    Set new_field = td.CreateField(“IntField”, dbInteger)
    td.Fields.Append new_field
    Set new_field = td.CreateField(“DateField”, dbDate)
    td.Fields.Append new_field

    ‘ Add the table to the database.
    db.TableDefs.Append td

A program also uses a TableDef object to attach a remote table to a local database. Again, the program uses the database object’s CreateTableDef method to create a new TableDef object. It should specify the name that the table should have locally in the call to CreateTableDef.

Next, the program should set the TableDef’s Connect property to indicate the connection information for the remote database. The SourceTableName property should give the name of the table in the remote database. This need not be the same as the name given to the table locally, though making the names the same may prevent some confusion.

Finally, the program should append the TableDef to the database’s TableDefs collection. The following code fragment attaches the remote table Employees on machine Beauty in the database file C:\Ch10\People.MDB. The local database is stored in Local.MDB. In this database the table will be known as LocalEmployees.

Dim db As Database
Dim td As TableDef

   ‘ Open the database.
   Set db = Workspaces(0).OpenDatabase(“Local.MDB”)

   ‘ Create a new TableDef.
   Set td = db.CreateTableDef(“LocalEmployees”)

   ‘ Set the connection information.
   td.Connect = “;DATABASE=\\Beauty\C\Ch10\People.MDB”
   td.SourceTableName = “Employees”
   
   ‘ Make the connection.
    db.TableDefs.Append td

This example is easy to generalize. The AttachTable subroutine, shown in the following code, takes the names of the remote database and table as parameters and attaches the table to the database TheDB. It makes the table’s local name the same as its remote name.

Sub AttachTable(table_name As String, db_name As String)
Dim td As TableDef

    Set td = TheDB.CreateTableDef(table_name)
    td.Connect = “;DATABASE=” & db_name
    td.SourceTableName = table_name
   
    TheDB.TableDefs.Append td
End Sub

The Attacher application in the Ch10\Attacher subdirectory of the compact disk uses this subroutine. The Main subroutine that follows attaches the remote database tables Employees, UserGroups, GroupPrivileges, and Passwords from the People.MDB database on machine Beauty.

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)
   
    ‘ Attach the tables.
    AttachTable “Employees”, REMOTE_DB
    AttachTable “UserGroups”, REMOTE_DB
    AttachTable “GroupPrivileges”, REMOTE_DB
    AttachTable “Passwords”, REMOTE_DB

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

This code attaches the tables to the local database Local.MDB. This database is empty—it contains no tables itself. It is used only to attach the remote tables.

To run this program on your network, you will need to make a few changes. First, copy the database file Ch10\Direct\People.MDB onto a remote computer that you can access from your computer. Next, change the definition of REMOTE_DB in the Attacher program’s Main subroutine so it indicates the file’s new location. Copy the file Ch10\PeopleW\Local.MDB onto your computer and change the definition of LOCAL_DB to indicate this file’s new location.

PeopleWatcher Remote Once remote tables have been attached to a local database, a program can use those tables as if they were present locally. To make the PeopleWatcher application use the tables attached by the Attacher program, the program needs a single change. Instead of opening the People.MDB database, PeopleWatcher Remote opens the local database Local.MDB. The PeopleWatcher application in subdirectory Ch10\PeopleW on the compact disk opens this database using the following code:

Const DB_FILE = “D:\Src\Ch10\PeopleW\Local.MDB”
    :
    Set TheDB = DBEngine.Workspaces(0).OpenDatabase(DB_FILE)

No other changes are needed to PeopleWatcher. To test this program on your computer, all you need to do is change the definition of DB_FILE to indicate the location of the local database Local.MBD.

The process is even simpler if the local (empty) and remote databases have exactly the same name. For example, PeopleWatcher’s databases could have been named C:\PeopleW\People.MDB on both computers. Then no changes would be necessary to PeopleWatcher. The only drawback to this method is that giving the databases the same name may make the process a little more confusing.

If the local and remote databases have the same name, making a distributed database application is a simple process:

1.  Write the application using a local database.

2.  Copy the application to other computers.

3.  Create an empty database on each of those computers.

4.  Attach the necessary tables in the original database to the empty databases.

Detaching Tables Detaching a remote table from a local database is even easier than attaching it. The program simply uses the Delete method of the database’s TableDefs collection to remove the local table. The program should specify the table to be deleted by name, as in the following code:

db.TableDefs.Delete “Employees”