ACC2000: How to Create and Refresh Linked dBASE Tables with ADOX (275250)

MORE INFORMATION

1. In Windows Explorer, create two new folders in the root directory of Drive C. Name one folder MyDbaseFiles and the other folder MyDbaseFiles2.
2. Close Windows Explorer, and then start Microsoft Access.
3. Open the sample database Northwind.mdb.
4. Click the Customers table, and then click Export on the File menu.
5. In the Export Table 'Customers' to dialog box, browse to the C:\MyDbaseFiles folder.
6. Set the Save as type box to dBASE IV (*.dbf).
7. Make sure the file name is Customers, and then click Save.
8. Close Northwind.mdb, and then create a new Access database, and name it DB1.mdb.
9. On the Insert menu, click Module. A new module appears.
10. On the Tools menu, click References.
11. Make sure the following two references are selected:
    • Microsoft ADO Ext. for DDL and Security. (Version 2.1 or higher)
    • Microsoft ActiveX Data Objects Library. (Version 2.1 or higher)
    
    
12. 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 = "LinkedBase"
    Set tbl.ParentCatalog = cat
    
    ' Set the properties to create the link.
    tbl.Properties("Jet OLEDB:Create Link") = True
    tbl.Properties("Jet OLEDB:Link Datasource") = strPath
    tbl.Properties("Jet OLEDB:Link Provider String") = strLinkPro
    tbl.Properties("Jet OLEDB:Remote Table Name") = 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 = "LINK" Then
            'Set the path apporpriately
            tbl.Properties("Jet OLEDB:Link Datasource") = strPath
        End If
    Next
    End Function
    					

13. Type the following line in the Immediate window, and then press ENTER:

    ?LinkdBaseFile("c:\MyDbaseFiles\","Dbase IV","Customer")

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

    ?RefreshLinkedDBase("C:\MyDbaseFiles2\")

21. Return to the Database window and try to open the dBASE table. Note that it opens without an error. The link is updated.