Link Table Path

[Jaro19a]

I have a split database and have a two-part question about updating the linked tables.

First, I would like to make it so if the front-end loads and the backend database path has changed, it will open a msgbox where you can type the new path for all the tables (linked).

Second, I would to make a button that would do the same if you knew the new location before hand.

I have no idea how I would do this, thank you for any help.

If any of you are ask why I need this, starting next week we are undergoing server upgrades and new file management standers.

 

[Autoeng]

I believe there is an example of this at www.rogersaccesslibrary.com under the downloads.

 

[fishonland]

You can do this using the following.

For the first Problem you can use the InputBox function like this to call the function below and relink the backend.

Call refreshlinks(InputBox("Please Specify the Database Path", "New Back End!"))

For the second problem create a Form with an unbound textbox or another inputbox that passes the path onto the following function with the new path of the backend database.

A couple of notes.
You will need the reference to the Microsoft ADO Ext. 2.7 for DLL and Security
You may need to add the "Option Explicit" to the top of the Module that you create the function in.


Function RefreshLinks(NewPath As String)
On Error GoTo ErrorHandler

Dim objCat As New ADOX.Catalog 'Define the ADOX Catalog Object
Dim objTbl As ADOX.Table 'Define the ADOX Table Object

'Open the catalog

objCat.ActiveConnection = CurrentProject.Connection
'Loop through the table collection and refresh the linked tables.
For Each objTbl In objCat.Tables
' Check to make sure the table is a linked table.
If objTbl.Type = "LINK" Then
objTbl.Properties("Jet OLEDB:Link Datasource") = NewPath
End If
Next
MsgBox "The links were successfully refreshed!!! "

ExitHandler:
Exit Function

ErrorHandler:
Select Case Err.number
Case -2147467259
Resume Next
Case Else
MsgBox Err.Description & " " & Err.number
Resume ExitHandler
End Select

End Function