No meu caso, o exemplo de samsmith não funcionou.
No entanto, corrigi-lo usando algo assim:
Dim DB As DAO.Database
Set DB = CurrentDb
Dim Tdf As TableDef
'Loops through list of tables
For Each Tdf In DB.TableDefs
If Tdf.SourceTableName <> "" Then
'this checks if it actually is a odbc table as i do not want to change the connection string of local tables obviously
If Not InStr(Tdf.Connect, "ODBC") = 0 And Not (Tdf.Attributes And
dbAttachedODBC) = 0 Then
Tdf.Connect = "ODBC;DSN=xxx;APP=MicrosoftR Access;WSID=A2200;DATABASE=xxx;Network=DBMSSOCN;TABLE=dbo." & Tdf.Name
Tdf.RefreshLink 'Saves the changes made
End If
End If
Next
Debug.Print "Finished!"
O Network=DBMSSOCN
é a parte chave, isso especifica que o tcp deve ser usado.