Visual Basic Codes
Visual Basic > Database SQL Stuff sample source codes

Repoint an Access Link table using ADO-ADOX

Repoint an Access Link table using ADO-ADOX

The following routine repoints a link table to a different database.

'Purpose     :  Updates the underlying source of a link table in an Access database
'Inputs      :  sLinkDatabasePath               The path to the database.
'               sLinkToNewDatabase              The path to the database repoint the link to.
'               sLinkTableName                  The name of the link table to repoint.
'Outputs     :  Returns True if succeeded in repointing the table
'Notes       :  Requires a reference to reference to both ADO (MS ActiveX Data Objects) and MSADOX.DLL
'               (MS ADO Ext. 2.5 DLL and Security).
'Revisions   :

Function AccessLinkTableUpdate(sLinkDatabasePath As String, sLinkToNewDatabase As String, sLinkTableName As String) As Boolean
    Dim catDB As ADOX.Catalog
    
    On Error GoTo ErrFailed
    Set catDB = New ADOX.Catalog
    'Open a catalog on the database which contains the table to refresh.
    catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & sLinkDatabasePath
    If catDB.Tables(sLinkTableName).Type = "LINK" Then
        catDB.Tables(sLinkTableName).Properties("Jet OLEDB:Link Datasource") = sLinkToNewDatabase
        AccessLinkTableUpdate = True
    End If
    Set catDB = Nothing
    Exit Function

ErrFailed:
    On Error GoTo 0
    AccessLinkTableUpdate = False
End Function
Privacy Policy | Link to Us | Links