How to Attach an External Database Table to a VB 3.0 Database
ID: Q108423
|
The information in this article applies to:
-
Microsoft Visual Basic Professional Edition 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:
- 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.
- 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.
- Repeat steps 3 and 4 for each external table.
- 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.
- Start a new project in Visual Basic. Form1 is created by default.
- 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
- Start the program or press the F5 key. To end program, close the form.
Example Two
- Start a new project in Visual Basic. Form1 is created by default.
- 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
- 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.
Additional query words:
3.00
Keywords : kbcode
Version : 3.00
Platform : WINDOWS
Issue type :
|