Here's code I have in the Load event of the first form to open in one of my
databases:

Private Sub Form_Load()
' Check that the front-end is linked to a back-end
On Error GoTo Err_Form_Load

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim strCurrLinkage As String
Dim strFrontendPath As String
Dim strExpectedBackend As String

 Set dbCurr = CurrentDb()
 strFrontendPath = Application.CurrentProject.Path
 If Right(strFrontendPath, 1) <> "\" Then
   strFrontendPath = strFrontendPath & "\"
 End If
 strExpectedBackend = ";Database=" & strFrontendPath & "CollectionData.mdb"

' Make the simplifying assumption that all linked tables
' will point to the same back-end
 For Each tdfCurr In dbCurr.TableDefs
   With tdfCurr
     strCurrLinkage = .Connect
     If Len(strCurrLinkage) > 0 Then
       If StrComp(strCurrLinkage, strExpectedBackend, vbTextCompare) <> 0
Then
         .Connect = strExpectedBackend
         .RefreshLink
       End If
     End If
   End With
 Next tdfCurr

End_Form_Load:
   Exit Sub

Err_Form_Load:
   MsgBox Err.Number & ": " & Err.Description
   Resume End_Form_Load

End Sub

Now, you wouldn't be able to make the simplifying assumption I made, but you
can modify that section a little to allow you to reset different tables to
point to different locations.