In modo molto più banale, queste poche righe di codice, sono più semplici:
Option Compare Database
Option Explicit
' Elenco delle Tabelle da LINKARE
Private Const cTableList = "Tabella1|Tabella2|Tabella3|Tabella4|Anagrafiche|Reparti|TabellaN"
Private Const cUSER = "USERNAME"
Private Const cPWD = "PASSWORD"
Private Const cSERVER="10.20.30.40\SQLEXPRESS"
Private Const cDBNAME="NomeDatabase"
Public Function RELINK()
Dim vTables As Variant
Dim vTable As Variant
vTables = Split(cTableList, "|")
For Each vTable In vTables
Call AttachDSNLessTable(CStr("dbo_" & vTable), "dbo." & vTable, cSERVER,cDBNAME , cUSER, cPWD)
Next
End Function
Private Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
On Error GoTo AttachDSNLessTable_Err
Dim td As TableDef
Dim stConnect As String
If IsTableExists(stLocalTableName) Then DoCmd.DeleteObject acTable, stLocalTableName
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
Set td = DBEngine(0)(0).CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
DBEngine(0)(0).TableDefs.Append td
AttachDSNLessTable = True
Exit Function
AttachDSNLessTable_Err:
AttachDSNLessTable = False
MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description
End Function
Public Function IsTableExists(ByVal strTableName As String) As Boolean
On Error Resume Next
Dim vName As Variant
vName = DBEngine(0)(0).TableDefs(strTableName).Name
IsTableExists = Err.Number = 0
End Function
Ovviamente la Funzione da chiamare è [RELINK()], puoi avere anche una LOCAL TABLE con le Tabelle, quindi apri un Recordset e lo cicli...
Questo codice è provato e funziona.