Microsoft KB Archive/108423

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

PSS ID Number: 108423

Article Last Modified on 11/7/2003

-

The information in this article applies to:


 * Microsoft Visual Basic Professional Edition for Windows 3.0

-



This article was previously published under Q108423



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:

  Create a variable for the Database object you are going to modify:   Dim Db As Database   Use the OpenDatabase function to open the existing Visual Basic or Microsoft Access database:   Set Db = OpenDatabase("BIBLIO.MDB")  Dimension a new TableDef object for the table from the external database. Set the following properties of the TableDef object to prepare for attaching the external table:

 Name property: A new name for the table to be used in Visual Basic. SourceTableName property: The original name of the external table or file name.</li> 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.</li></ol> </li> Repeat steps 3 and 4 for each external table.</li> 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.</li></ol>

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.

<ol> Start a new project in Visual Basic. Form1 is created by default.</li>  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 </li> Start the program or press the F5 key. To end program, close the form.</li></ol>

Example Two
<ol> Start a new project in Visual Basic. Form1 is created by default.</li>  Double-click the form to open its code window. Add the following code to the Form Load event.

Note You must change Username= and PWD = to the correct values before you run this code. Make sure that Username has the appropriate permissions to perform this operation on the database. 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;Username= ;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 </li> 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.</li></ol>

<div class="references_section">