How To Link and Refresh Linked Jet Tables Using ADOX

Article translations Article translations
Article ID: 230588 - View products that this article applies to.
This article was previously published under Q230588
Expand all | Collapse all

Summary

You can update the connection information for a linked table by using the Data Access Objects (DAO) RefreshLink method. This article describes another way to do this with ActiveX Data Objects (ADO) version 2.1 and later by using the Tables collection exposed by the ADO Extensibility model (ADOX).

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.
The cmdCreateLinkedTable() procedure creates a linked table, Linked_Employees, from the Northwind sample database Employees table. The cmdRefreshLink() procedure refreshes the linked tables with a new database location.

References

240222 How To Use ADO to Refresh/Create Linked Table for Password Secured Jet 4.0 Database
 

Properties

Article ID: 230588 - Last Review: January 23, 2013 - Revision: 2.0
Applies to
  • Microsoft ActiveX Data Objects 2.7
Keywords: 
kbhowto KB230588
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com