Relinking to your access database
When you are developing an application in house you can define the directory where the backend database will sit. But if your application is used in a number of offices the users will need to relink the program to the source of there database.
I got this code from a developer in the UK in the late 90’s cant remember his name
To use the code open a form and add a text box which the user will enter the path to the file and a button called “relink”
Add an onclick code event to the button and insert the following code
Private Sub cmdRefresh_Click() On Error GoTo cmdRefreshErr Dim sTest As String, dblocal As Database Dim tdlocal As TableDef On Error Resume Next sTest = Dir(Me![txtFilename]) On Error GoTo cmdRefreshErr If Len(sTest) = 0 Then MsgBox "File not found, Please try again.", vbExclamation, "Link to new Data file" ElseIf TablesMatch(Me![txtFilename]) Then Set dblocal = CurrentDb DoCmd.Hourglass True For Each tdlocal In dblocal.TableDefs If Len(tdlocal.Connect) > 0 Then DoCmd.Echo True, "Linking " & tdlocal.Name tdlocal.Connect = ";DATABASE=" & Trim(Me![txtFilename]) tdlocal.RefreshLink End If Next DoCmd.Echo True, "Done" DoCmd.Hourglass False MsgBox "Linking to new back-end data file was successful." DoCmd.Close acForm, Me.Name If IsLoaded("frmLoading") Then DoCmd.Close acForm, "frmLoading" DoCmd.OpenForm "frmLoading" End If Else MsgBox "The tables in the data file " & Me![txtFilename] & " didn't match the current database" End If ExitcmdRefreshErr: DoCmd.Echo True DoCmd.Hourglass False Exit Sub cmdRefreshErr: Select Case Err Case Else MsgBox Err.Number & " - " & Err.descripton Resume ExitcmdRefreshErr End Select End Sub
Leave a ReplyWant to join the discussion?
Feel free to contribute!