ACC2000: How to Use ADOX to Create and Refresh Linked Jet Tables (275249)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Example

1. In a blank Access database, create a new module.
2. In the Visual Basic Editor, make sure that the following references are selected:
   • Microsoft ADO Ext. for DDL and Security. (Version 2.1 or later)
   • Microsoft ActiveX Data Objects Library. (Version 2.1 or later)
3. Type or paste the following code into the new module:

   Sub CreateLinkedJetTable()
   Dim cat As ADOX.Catalog
   Dim tbl As ADOX.Table
   
   Set cat = New ADOX.Catalog
   
   ' Open the catalog.
   cat.ActiveConnection = CurrentProject.Connection
   
   Set tbl = New ADOX.Table
   
   ' 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") = "C:\Program Files\Microsoft Office\Office\Samples\northwind.mdb"
   tbl.Properties("Jet OLEDB:Remote Table Name") = "Employees"
   tbl.Properties("Jet OLEDB:Create Link") = True
   
   ' To link a table with a database password set the Link Provider String
   ' tbl.Properties("Jet OLEDB:Link Provider String") = "MS Access;PWD=Admin;"
   
   ' Append the table to the tables collection.
   cat.Tables.Append tbl
   Set cat = Nothing
   
   End Sub
   
   Sub RefreshLinks()
   Dim cat As ADOX.Catalog
   Dim tbl As ADOX.Table
   
   Set cat = New ADOX.Catalog
   
   ' Open the catalog.
   cat.ActiveConnection = CurrentProject.Connection
   
   Set tbl = New ADOX.Table
   
   For Each tbl In cat.Tables
   ' Verify that the table is a linked table.
       If tbl.Type = "LINK" Then
           tbl.Properties("Jet OLEDB:Link Datasource") = "C:\Program Files\Microsoft Office\Office\Samples\northwind.mdb"
   ' To refresh a linked table with a database password set the Link Provider String
   'tbl.Properties("Jet OLEDB:Link Provider String") = "MS Access;PWD=Admin;"
       End If
   Next
   End Sub
   					

4. To link the table, type the following in the Immediate window, and then press ENTER:

   CreateLinkedJetTable

   Note that a new table named Linked_Employees is added to the database.
5. To refresh the linked table, type the following in the Immediate window, and then press ENTER:

   RefreshLinks