How to Attach an External Database Table to a VB 3.0 Database

- Professional Edition of Microsoft Visual Basic for Windows, version 3.0

SUMMARY

An attached table is a table from an external database linked at run time to a Microsoft Access database. You can gain access to the data in the attached table by using a data control, a Dynaset, or a Snapshot. The database format native to Visual Basic is the Microsoft Access format.

Using the data control, you can open a Dynaset on an external table. Specify the external database type in the Connect property. Specify an appropriate directory or file name in the DatabaseName property of the data control.

Using object variables, you can attach a table from any supported external database to a Microsoft Access database as shown in the examples below.

NOTE: You can use queries and the move and find methods on an attached table. An attached table cannot be opened with the OpenTable method. Therefore you cannot use the Seek method on an attached table.

MORE INFORMATION

The following steps describe how to attach a table to an existing Visual Basic database or a Microsoft Access database:

1. Create a variable for the Database object you are going to modify:

   Dim Db As Database

2. Use the OpenDatabase function to open the existing Visual Basic or Microsoft Access database:

   Set Db = OpenDatabase("BIBLIO.MDB")

3. Dimension a new TableDef object for the table from the external database.

4. Set the following properties of the TableDef object to prepare for attaching the external table:

   a. Name property: A new name for the table to be used in Visual Basic.

   b. SourceTableName property: The original name of the external table or

         file name.
   

   c. Connect property: The database type and other parameters. If a

         password is required but not provided in the Connect property, a
         Login dialog box appears each time the table is accessed.
   

5. Repeat steps 3 and 4 for each external table.

6. Use the Append method to add the TableDef object(s) to the TableDefs collection of the Microsoft Access database. This step actually creates the object links in the Microsoft Access database file.

Example One

Both databases shown below are Microsoft Access databases. But the table to be attached to the Microsoft Access database could be from any of the other database formats that Visual Basic version 3.0 supports.

1. Start a new project in Visual Basic. Form1 is created by default.

2. Double-click the form to open its code window. Add the following code to the Form Load event:

      Sub Form_Load ()
         Dim db As database
         Dim td As New Tabledef
         Dim ds As dynaset
         Set db = OpenDatabase("BIBLIO.MDB")
         td.Name = "MyNewCustomersTable"   ' New Table name for use in VB.
         td.SourceTableName = "Customers"  ' Table name in source database.
         td.Connect = ";DATABASE=c:\access\nwind.mdb;"   ' Source database.
         db.TableDefs.Append td   ' Append Customers Table to BIBLIO.MDB.
         Set ds = db.CreateDynaset("MyNewCustomersTable")  ' Create dynaset.
         Debug.Print ds.Fields(0)     ' Proves the Table is attached.
         Debug.Print ds.Fields(1)     ' Proves the Table is attached.
         Debug.Print ds.Fields(2)     ' Proves the Table is attached.
         ' The following statement deletes the appended Table, if desired:
         db.TableDefs.Delete "MyNewCustomersTable"
      End Sub
   
   

3. Start the program or press the F5 key. To end program, close the form.

Example Two

1. Start a new project in Visual Basic. Form1 is created by default.

2. Double-click the form to open its code window. Add the following code to the Form Load event:

      Sub Form_Load ()
   
         Dim db1 As database, db2 As database
         Dim td As New Tabledef
         Dim tb As Table
         Dim ds As dynaset
         Dim f1 As New field, f2 As New field
   
         Const DB_LANG_GENERAL = ";LANGID=0x0809;CP=1252;COUNTRY=0"
         Const DB_VERSION10 = 1
         Const file1 = "test1.mdb"  'contains Table food1"
         Const file2 = "test2.mdb"  'contains Table food2
   
         Set db1 = OpenDatabase(file1)
         Set db2 = OpenDatabase(file2)
         ' db2.TableDefs.Delete "new_food1" ' Deletes Table if desired.
   
         td.Name = "new_food1"
         td.SourceTableName = "food1"
         td.Connect = ";database=" & file1 & ";"
   
         ' NOTE: For an ODBC database, the connect string would be similar to:
         ' td.Connect = "ODBC;UID=sa;PWD=;DSN=texas;DATABASE=pubs;"
         ' td.Attributes = DB_ATTACHEDTABLE
         ' or, if password protected: td.Attributes = &H20000
         ' or, if exclusive: td.Attributes = tbl.Attributes + &H10000
   
         db2.TableDefs.Append td   ' Attaches the external Table.
   
         ' NOTE: The OpenTable method is illegal for attached Tables:
         '  Set tb = db2.OpenTable("new_food1")   ' Gives an error.
   
         Set ds = db2.CreateDynaset("new_food1")
         Print ds.Fields(0)     ' Proves the Table is attached.
         ds.Close
         db1.Close
         db2.Close
      End Sub
   
   

3. Modify the code to use your existing database and table names. Start the program or press the F5 key. To end the program, close the form.

REFERENCES

• See the "attached tables" topic in the Help menu.
• See the EXTERNAL.TXT file provided with Visual Basic.
• The VISDATA.MAK file installed in the VB3\SAMPLES\VISDATA directory loads extensive examples of data access. The VISDATA sample program uses every data access function in Visual Basic. You can refer to the VISDATA source code for examples of how to use each data access function.