Microsoft KB Archive/275250

= ACC2000: How to Create and Refresh Linked dBASE Tables with ADOX =

Article ID: 275250

Article Last Modified on 6/28/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q275250



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

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



SUMMARY
With ActiveX Data Objects code, you can programmatically link to dBASE tables and also refresh them. This article contains sample Visual Basic for Applications functions that demonstrate how to do this.



MORE INFORMATION
 In Windows Explorer, create two new folders in the root directory of Drive C. Name one folder MyDbaseFiles and the other folder MyDbaseFiles2. Close Windows Explorer, and then start Microsoft Access. Open the sample database Northwind.mdb. Click the Customers table, and then click Export on the File menu. In the Export Table 'Customers' to dialog box, browse to the C:\MyDbaseFiles folder. Set the Save as type box to dBASE IV (*.dbf).</li> Make sure the file name is Customers, and then click Save.</li> Close Northwind.mdb, and then create a new Access database, and name it DB1.mdb.</li> On the Insert menu, click Module. A new module appears.</li> On the Tools menu, click References.</li> Make sure the following two references are selected: <ul> Microsoft ADO Ext. for DDL and Security. (Version 2.1 or higher)</li> Microsoft ActiveX Data Objects Library. (Version 2.1 or higher)</li></ul>

</li>  Type or paste the following code into the new module: Function LinkdBaseFile(strPath As String, _ strLinkPro As String, strRemTab As String)

Dim conn As New ADODB.Connection Dim cat As New ADOX.Catalog Dim tbl As New ADOX.Table

' Open the catalog using the current Access database. cat.ActiveConnection = CurrentProject.Connection

' Create the new table. tbl.Name = &quot;LinkedBase&quot; Set tbl.ParentCatalog = cat

' Set the properties to create the link. tbl.Properties(&quot;Jet OLEDB:Create Link&quot;) = True tbl.Properties(&quot;Jet OLEDB:Link Datasource&quot;) = strPath tbl.Properties(&quot;Jet OLEDB:Link Provider String&quot;) = strLinkPro tbl.Properties(&quot;Jet OLEDB:Remote Table Name&quot;) = strRemTab

' Append the table to the tables collection of the catalog. cat.Tables.Append tbl

' Clean up. Set cat = Nothing

End Function

Function RefreshLinkedDBase(strPath As String)

Dim cat As New ADOX.Catalog Dim tbl As New ADOX.Table

' Open the catalog. cat.ActiveConnection = CurrentProject.Connection

'Cycle through all tables. For Each tbl In cat.Tables ' Check to make sure each table is a linked table. If tbl.Type = &quot;LINK&quot; Then 'Set the path apporpriately tbl.Properties(&quot;Jet OLEDB:Link Datasource&quot;) = strPath End If Next End Function </li> Type the following line in the Immediate window, and then press ENTER:

?LinkdBaseFile(&quot;c:\MyDbaseFiles\&quot;,&quot;Dbase IV&quot;,&quot;Customer&quot;)

</li> In the Database window, click Tables, and on the View menu click Refresh. Note that there is a new linked dBASE table.</li> In Windows Explorer, browse to the C:\MyDbaseFiles\ folder.</li> Click the file CUSTOMER.DBF, and on the Edit menu, click Cut.</li> Browse to the C:\MyDbaseFiles2 folder and paste the file there.</li> Return to the DB1.mdb database and try opening the dBASE table. Note that you receive an error. The link must be updated.</li> Return to the Visual Basic for Applications module that you created earlier.</li> Type the following line in the Immediate window, and then press ENTER:

?RefreshLinkedDBase(&quot;C:\MyDbaseFiles2\&quot;)

</li> <li>Return to the Database window and try to open the dBASE table. Note that it opens without an error. The link is updated.</li></ol>

Additional query words: directory directories

Keywords: kbhowto KB275250

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.