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 = "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
- To link the table, type the following in the Immediate window, and then press ENTER:
CreateLinkedJetTable
- To refresh the linked table, type the following in the Immediate window, and then press ENTER:
RefreshLinks
REFERENCES
For more information about ADOX, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type adox in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
-or-
Search on adox on the Microsoft Developers Network (MSDN) Web site. You can find the Microsoft Developers Network (MSDN) at the following Microsoft Web site:
Keywords: kbhowto KB275249