Setting Linked Database (MS Access) path without a

Question

I have an Access system that comprises two parts: a "frontend" .mdb file that contains forms, reports and macros, and a backend .mdb file that contains the data. Copies of the frontend MDB files are stored on each computer and the backend file is located at \\server\share\backend.mdb. The frontend MDB files use the Linked Table feature of Access to connect to the backend MDB.

I recently made some changes to the MDB on my home network, I copied both files to my machine and was able to change the Linked Table path because I had the backend file on my local computer. However now I need to put the updated frontend MDB back on the (remote) client's network, however there is no way for me to remote-in to change the Linked Table path back to \\server\shares\backend.mdb.

Is there any way to set the Linked Table path (on my local computer) to a path that doesn't exist? The Access GUI only lets me set it through a File Open dialog and so doesn't let me manually set it.

I'm using Access 2010, though the client uses Access 2003 and Access 2013.

Answer 1

I use this code to link to another backend file (say if I deliver and the backend is not in the same place, like it rarely is)

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

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)
                    Exit Function
                End If

                On Error GoTo 0
            End If
        Next

    Set tdf = Nothing
    Set db = Nothing
End Function

Then when I open my default form when the DB opens I try to connect to the backend

On Error Resume Next
    Dim rs As DAO.Recordset: Set rs = CurrentDb.OpenRecordset("Select Username, Password, UserGroup FROM Users")
    If Err.Number = 3024 Or Err.Number = 3044 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
    End If