VB and VBA Users Source Code: Repoint an Access Link table using ADO/ADOX

VB/VBA Source Code
Repoint an Access Link table using ADO/ADOX

By:	Andrew Baker
Email (spam proof):	Email the originator of this post
Date:	Saturday, March 24, 2001
Hits:	2060
Category:	Office

Article:
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 'Author : Andrew Baker 'Date : 03/09/2000 14:17 '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
Terms and Conditions
Support this site Download a trial version of the Excel Workbook Rebuilder