Io solitamente mi comporto così:
Mi creo una Tabella locale in Access chiamata [_LinkedTables] nella quale inserisco i Nomi delle Tabelle da LINKARE sul SERVER.
A questo punto su apertura dell'applicativo chiamo la Routine che RIPRISTINA le connessioni:
Private Const cCnnString as String="ODBC;DRIVER={SQL Server};SERVER=NOME_SERVER;UID=USER_NAME;PWD=PASSWORD;DATABASE=DB_NAME;LANGUAGE=Italiano;
Private Const cLnkTbl As String = "_LinkedTables"
Private Function LinkODBCTbl() As Boolean
On Error GoTo Err_RlnkODBCTbl
Dim tdf As DAO.TableDef
Dim rs As DAO.Recordset
Dim S As String
Dim strSQL As String
LinkODBCTbl = False
strSQL = cLnkTbl
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveFirst
Do Until rs.EOF
S = rs.Fields(0).value
If fTableExist(S) Then CurrentDb.TableDefs.Delete S
Set tdf = CurrentDb.CreateTableDef(S)
tdf.Connect = cCnnString
tdf.SourceTableName = S
CurrentDb.TableDefs.Append tdf
Set tdf = Nothing
rs.MoveNext
Loop
LinkODBCTbl = True
Exit_Here:
rs.Close
Set rs = Nothing
Set tdf = Nothing
Exit Function
Err_RlnkODBCTbl:
LinkODBCTbl = False
MsgBox "Impossibile connettersi al Server"
Resume Exit_Here
End Function
Function fTableExist(strName As String) As Boolean
On Error GoTo ERR_Exist
Dim obj As Object
Set obj = CurrentDb.TableDefs(strName)
fTableExist = True
Exit_Here:
Exit Function
ERR_Exist:
fTableExist = False
Resume Exit_Here
End Function
Una cosa simile... provala bene perchè l'ho scritta senza riverificarla...