Relinking back-end tables

If you've split your database into front-end and back-end, you will from time-to-time need to update the links between the two. For me the most common reason for doing this is that I'm installing an update to the front-end. I build new releases in a development/test environment (where the front-end tables are linked to a test copy of the back-end), and when I'm ready, I copy a new version of the front-end to the server and update the table links so it's connected to the production back-end.

(If you haven't split your database into front-end and back-end and wonder if you should, see Sharing an Access Database.)

Relinking to the back-end tables manually can be time consuming and error prone. You'll probbaly want to adopt some automated way to do the relinking. Here are some that I've found.

Relink Access tables from code
This one from The Access Web is quite nice in that if it does a little hunting to find the table you need to link to. I haven't used it, but code from The access Web is always worth the time it takes to incorporate it into your database.

Relink tables from different datasources
An alternative version from The access Web, this one handles multiple back-ends.

Re-Linking Back-End Tables
This is from the Utteraccess code archive and includes a nice form to manage the relinking.

How to relink back-end tables with the common dialog control in Access 2000
I think this is the original article that the above posts mean to improve upon.

And now here's my version. It's simple and straightforward. It assumes the back-end will be in the same folder as the front-end and that all the linked tables will be linked to the same back-end. It looks in the custom properties of the front-end for the name of the back-end database (File/Database Properties/Custom, create a property called BEDBName.)

Public Sub UpdateTableLinks()
  Dim varThis As Variant
  Dim strThisFolder As String
  Dim strBEFileSpec As String
  
  strThisFolder = CurrentProject.Path
  strBEFileSpec = strThisFolder & "\" & GetDBCustomProperty("BEDBName")
  If MsgBox(Prompt:="Update remote table links to " & strBEFileSpec & "?", _
      Buttons:=vbYesNo + vbQuestion + vbDefaultButton2) = vbYes Then
    For Each varThis In CurrentDb.TableDefs
      With varThis
        If Trim(Nz(.Connect)) Like ";DATABASE=*" Then
          .Connect = ";DATABASE=" & strBEFileSpec
          .RefreshLink
        End If
      End With
    Next varThis
    MsgBox "Relink complete"
  End If
End Sub

Public Function GetDBCustomProperty(ByVal strPropertyName As String) As String
  GetDBCustomProperty = CurrentDb.Containers!Databases.Documents!UserDefined.Properties(strPropertyName)
End Function