Because you should be splitting your Access database before you give it
to a customer to use you can oftern run into the problem of your app not
finding that backend file. This is what I do to always ensure my front
end points to the right data
I generally use an AutoExec macro to apply some basic settings when
the app starts up (hiding the navigation pane, disabling full menus
maybe, minimizing the ribbon maybe). At the end of all this I check to
make sure the backend data re in place. The general process is to try to
open a record set from a table you know exists. If that succeeds then
super! you're conencted to your backend already. If that fails then you
are probably not connected and you should take some action.
Private Sub VeryifyBackendIsInPlace()
Dim rst As DAO.Recordset
Dim ITEM_NOT_FOUND_IN_COLLECTION_ERROR As Integer, FILE_NOT_FOUND_ERROR As Integer
Dim INVALID_PATH_ERROR As Integer, NETWORK_CONNECTION_ERROR As Integer
ITEM_NOT_FOUND_IN_COLLECTION_ERROR = 3265
FILE_NOT_FOUND_ERROR = 3024
INVALID_PATH_ERROR = 3044
NETWORK_CONNECTION_ERROR = 3043
On Error Resume Next
Set rst = CurrentDb.OpenRecordset("Select top 1 * from Table_I_Know_Exists")
If Err.Number = FILE_NOT_FOUND_ERROR Or Err.Number = INVALID_PATH_ERROR Then
MsgBox Err.Description & vbNewLine & "You will be prompted next to locate the data file. Without this file the database cannot open." _
, vbCritical + vbOKOnly, "Backend Data File Not Found"
GoTo FindBackEndFile
ElseIf Err.Number = NETWORK_CONNECTION_ERROR Then
If MsgBox(Err.Description & vbNewLine & "Would you like to connect to another data source", vbYesNo, "Network error") = vbYes Then
GoTo FindBackEndFile
End If
ElseIf Err.Number <> 0 Then
MsgBox Err.Description, , "Could load the data"
End If
rst.Close
On Error GoTo 0
Exit Sub
FindBackEndFile:
AttachToAnotherDataFile
End Sub
You see if we get an assortment of network/file connection issues I prompt the user to go look for the backend data file.
Public Function AttachToAnotherDataFile() As Boolean
On Error GoTo 0
Dim ofd As FileDialog
Dim result As VbMsgBoxResult
Set ofd = FileDialog(msoFileDialogFilePicker)
ofd.show
If ofd.SelectedItems.Count = 1 Then
result = RelinkLinedTablesToBackend(ofd.SelectedItems(1))
If result = vbCancel Then
AttachToAnotherDataFile = False
End If
AttachToAnotherDataFile = True
Else
AttachToAnotherDataFile = False
End If
End Function
Then I relink all the linked tables to it.
Function RelinkLinedTablesToBackend(backendPath As String) As VbMsgBoxResult
Dim tdf As TableDef
Dim db As Database
Dim tdfRefresh As TableDef
Set db = CurrentDb
For Each tdf In CurrentDb.TableDefs
If tdf.Connect <> vbNullString Then
On Error Resume Next
db.TableDefs(tdf.Name).Connect = ";DATABASE=" & backendPath
db.TableDefs(tdf.Name).RefreshLink
If Err.Number <> 0 Then
RelinkLinedTablesToBackend = MsgBox(Err.Description, vbCritical + vbRetryCancel, "Error #:" & Err.Number)
End If
'' RelinkLinedTablesToBackend = VbMsgBoxResult.vbOK
On Error GoTo 0
End If
Next
Set tdf = Nothing
Set db = Nothing
End Function
This strategy only works if all the linked tables are in the same file.
I could update it so that it only updates those linked tables which it could not conenct to.