There's code to re-link ODBC tables at the following URL ...
http://www.mvps.org/access/tables/tbl0010.htm

In my experience, deleting and re-creating the links is a very sensible
thing to do - and is especially necessary if you have made any changes to
the SQL Server data structure.
There are many slight variations on this coding, but here is an example I
wrote where the table names needed to be re-named as per your example.  The
table names are hard-coded which has advantages and disadvantages, but could
easily be changed to look for existing tables in the database.

Public Function ReLinkTables() As Boolean

   On Error GoTo Err_Handler

   Dim strConnect As String
   Dim strTableList As String

   strConnect = "ODBC;Driver={SQL Server};" & _
                "Server=MyServer;" & _
                "Database=MyDatabase;" & _
                "Uid=MyLogin;Pwd=MyPassword;"

   ' Table list is in pairs like "SQL_Server|Link_Table;"
   strTableList = "dbo.Customer|tblCustomers;" & _
                  "dbo.Product|tblProducts;" & _
                  "dbo.Order|tblOrders"

   If DeleteLinks(strTableList) Then
       If LinkTables(strTableList, strConnect) Then
           ReLinkTables = True
       End If
   End If

Exit_Handler:
   Exit Function

Err_Handler:
   MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
   Resume Exit_Handler

End Function

Private Function LinkTables(strTableList As String, strConnect As String) As
Boolean

   On Error GoTo Err_Handler

   Dim dbs As DAO.Database
   Dim tdf As DAO.TableDef
   Dim astrTables() As String
   Dim strTwoTables As String
   Dim strSqlServerTable As String
   Dim strLinkTable As String
   Dim lngPosition As Long
   Dim lngCount As Long

   Set dbs = CurrentDb

   astrTables = Split(strTableList, ";")

   For lngCount = 0 To UBound(astrTables())

       strTwoTables = astrTables(lngCount)

       lngPosition = InStr(strTwoTables, "|")

       If (lngPosition > 0) And (Len(strTwoTables) > lngPosition) Then

           strSqlServerTable = Mid$(strTwoTables, 1, lngPosition - 1)

           strLinkTable = Mid$(strTwoTables, lngPosition + 1)

           Set tdf = dbs.CreateTableDef(strLinkTable, dbAttachSavePWD)

           tdf.Connect = strConnect

           tdf.SourceTableName = strSqlServerTable

           dbs.TableDefs.Append tdf

           Set tdf = Nothing

       End If

   Next lngCount

   LinkTables = True

Exit_Handler:

   On Error Resume Next

   If Not tdf Is Nothing Then
       Set tdf = Nothing
   End If

   If Not tdf Is Nothing Then
       Set dbs = Nothing
   End If

   Application.RefreshDatabaseWindow

   Exit Function

Err_Handler:
   MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
   Resume Exit_Handler

End Function

Private Function DeleteLinks(strTableList As String) As Boolean

   On Error GoTo Err_Handler

   Dim dbs As DAO.Database
   Dim tdf As DAO.TableDef
   Dim astrTables() As String
   Dim strTwoTables As String
   Dim strLinkTable As String
   Dim lngPosition As Long
   Dim lngCount As Long
   Dim blnError As Boolean

   Set dbs = CurrentDb

   astrTables = Split(strTableList, ";")

   For lngCount = 0 To UBound(astrTables())

       strTwoTables = astrTables(lngCount)

       lngPosition = InStr(strTwoTables, "|")

       If (lngPosition > 0) And (Len(strTwoTables) > lngPosition) Then

           strLinkTable = Mid$(strTwoTables, lngPosition + 1)

           If TableExists(strLinkTable) Then

               Set tdf = dbs.TableDefs(strLinkTable)

               If Len(tdf.Connect) > 0 Then
                   dbs.TableDefs.Delete tdf.Name
               Else
                   ' This is not a linked table
                   MsgBox "Cannot delete table '" & strLinkTable & "'",
vbExclamation
                   blnError = True
               End If

               Set tdf = Nothing

           End If

       End If

   Next lngCount

   If Not blnError Then
       DeleteLinks = True
   End If

Exit_Handler:

   On Error Resume Next

   dbs.TableDefs.Refresh

   If Not dbs Is Nothing Then
       Set dbs = Nothing
   End If

   Application.RefreshDatabaseWindow

   Exit Function

Err_Handler:
   MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
   Resume Exit_Handler

End Function

Private Function TableExists(strTableName As String) As Boolean

   On Error GoTo Err_Handler

   Dim dbs As DAO.Database
   Dim tdf As DAO.TableDef

   Set dbs = CurrentDb

   For Each tdf In dbs.TableDefs
       If tdf.Name = strTableName Then
           TableExists = True
           Exit For
       End If
   Next tdf

Exit_Handler:

   On Error Resume Next

   If Not dbs Is Nothing Then
       Set dbs = Nothing
   End If

   Exit Function

Err_Handler:
   MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
   Resume Exit_Handler

End Function