Microsoft KB Archive/275249

= ACC2000: How to Use ADOX to Create and Refresh Linked Jet Tables =

Article ID: 275249

Article Last Modified on 7/27/2006

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q275249



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).



SUMMARY
This article shows you how to create and refresh linked tables by using Microsoft ADO Ext. 2.x for DDL and Security (ADOX), where x is 1 or later.



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
 In a blank Access database, create a new module. 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)   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 = &quot;Linked_Employees&quot; Set tbl.ParentCatalog = cat

' Set the properties to create the link. tbl.Properties(&quot;Jet OLEDB:Link Datasource&quot;) = &quot;C:\Program Files\Microsoft Office\Office\Samples\northwind.mdb&quot; tbl.Properties(&quot;Jet OLEDB:Remote Table Name&quot;) = &quot;Employees&quot; tbl.Properties(&quot;Jet OLEDB:Create Link&quot;) = True

' To link a table with a database password set the Link Provider String ' tbl.Properties(&quot;Jet OLEDB:Link Provider String&quot;) = &quot;MS Access;PWD=Admin;&quot;

' 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 = &quot;LINK&quot; Then tbl.Properties(&quot;Jet OLEDB:Link Datasource&quot;) = &quot;C:\Program Files\Microsoft Office\Office\Samples\northwind.mdb&quot; ' To refresh a linked table with a database password set the Link Provider String 'tbl.Properties(&quot;Jet OLEDB:Link Provider String&quot;) = &quot;MS Access;PWD=Admin;&quot; End If Next End Sub </li> 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.</li> To refresh the linked table, type the following in the Immediate window, and then press ENTER:

RefreshLinks

</li></ol>

<div class="references_section">