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

MORE INFORMATION

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:
   
   
   1. Name property: A new name for the table to be used in Visual Basic.
   2. SourceTableName property: The original name of the external table or file name.
   3. 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

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.
   
   Note You must change Username= <username> and PWD =<strong password> 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=<username>;PWD=<strong password>;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.