Microsoft KB Archive/108423

From BetaArchive Wiki

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:

  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

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.

    Note You must change Username= <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=<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.


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: KB108423
Technology: kbAudDeveloper kbVB300 kbVB300Search kbVBSearch kbZNotKeyword2 kbZNotKeyword6