Linking a Microsoft Access Table

In Microsoft Office Access 2003, you can create links to tables in Access databases as well as other data formats supported by Microsoft Jet's installable drivers (for example: Excel, dBASE, Paradox, Exchange/Outlook, Lotus, Text, and HTML) and ODBC drivers.

To create a linked Access table, you have to set the following table properties:

Jet OLEDB:LinkDatasource Jet OLEDB:Remote Table Name Jet OLEDB:CreateLink

The procedure in Hands-On 11-8 demonstrates how to establish a link to the Employees table located in the Northwind database.

©Hands-On 11-8: Linking a Microsoft Jet Table

1. In the Database window, press Alt+F11 to switch to the Visual Basic Editor window.

2. In the Visual Basic Editor window, choose Insert | Module.

3. In the module's Code window, type the Link_JetTable procedure shown below.

4. Choose Run | Run Sub/UserForm to execute the procedure.

5. To access the linked Employees table after running this procedure, be sure to refresh the Database window by pressing F5.

Sub Link_JetTable()

Dim cat As ADOX.Catalog Dim lnkTbl As ADOX.Table Dim strDb As String Dim strTable As String

On Error GoTo ErrorHandler strDb = CurrentProject.Path & "\Northwind.mdb" strTable = "Employees" Set cat = New ADOX.Catalog cat.ActiveConnection = CurrentProject.Connection

Set lnkTbl = New ADOX.Table With lnkTbl

' Name the new Table and set its ParentCatalog property to the ' open Catalog to allow access to the Properties collection. .Name = strTable Set .ParentCatalog = cat

Creating and Manipulating Databases with ADO

' Set the properties to create the link .Properties("Jet OLEDB:Create Link") = True .Properties("Jet OLEDB:Link Datasource") = strDb .Properties("Jet OLEDB:Remote Table Name") = strTable End With

' Append the table to the Tables collection cat.Tables.Append lnkTbl

Set cat = Nothing

MsgBox "The current database contains a linked " & _

"table named " & strTable Exit Sub

ErrorHandler:

MsgBox Err.Number & ": " & Err.Description End Sub

0 -1

Post a comment