How To Link and Refresh Linked Jet Tables Using ADOX (230588)

MORE INFORMATION

1. In Microsoft Visual Basic, create a new Standard EXE project. Form1 is added to the project by default.
2. On the Project menu, click References. From the list of available components, select Microsoft ActiveX Data Objects 2.1 Library and Microsoft ADO Ext. 2.1 for DDL and Security.
3. Place two CommandButton controls on Form1: cmdRefreshLink and cmdCreateLinkedTable.
4. Paste the following code in the Declarations section of Form1:

   Option Explicit
   
   Dim cn As ADODB.Connection
   
   Private Sub Form_Load()
      Set cn = New ADODB.Connection
      cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Db1.mdb"
   End Sub
   
   Private Sub cmdCreateLinkedTable_Click()
      Dim cat As ADOX.Catalog
      Dim tbl As ADOX.Table
    
      Set cat = New ADOX.Catalog
      Set tbl = New ADOX.Table
   
      ' Open the catalog.
      cat.ActiveConnection = cn
    
      ' Create the new Table.
      tbl.Name = "Linked_Employees"
      Set tbl.ParentCatalog = cat
   
      ' Set the properties to create the link.
      tbl.Properties("Jet OLEDB:Link Datasource") = "D:\nwind.mdb"
      tbl.Properties("Jet OLEDB:Remote Table Name") = "Employees"
      tbl.Properties("Jet OLEDB:Create Link") = True
   
      ' Append the table to the Tables collection.
      cat.Tables.Append tbl
      Set cat = Nothing
   End Sub
   
   Private Sub cmdRefreshLink_Click()
      Dim cat As ADOX.Catalog
      Dim tbl As ADOX.Table
    
      Set cat = New ADOX.Catalog
      Set tbl = New ADOX.Table
    
      ' Open the catalog.
      cat.ActiveConnection = cn
      For Each tbl In cat.Tables
         If tbl.Type = "LINK" And tbl.Name = "Linked_Employees" Then
            tbl.Properties("Jet OLEDB:Link Datasource") = "D:\OtherSource.mdb"
         End If
      Next
   End Sub
   					

5. Modify the code in the Form_Load() event to point the connection string to a valid Database in the cn.Open method.
6. Modify the code in the cmdCreateLinkedTable_Click() event to point the connection string to the Northwind sample database in the tbl.Properties("Jet OLEDB:Link Datasource") assignment.
7. Modify the code in the cmdRefreshLink_Click() event to point the tbl.Properties("Jet OLEDB:Link Datasource") assignment to another valid location.
8. Run the project.