Your login to SQL server is presumably by user name and password I take it as opposed to windows network authentication therefore I assume you have some mechanism in your frontend to cater for that logging in process.
You could have a DSN-less reference in code to do the connection for you (this reduces the need to get others to create DSN,s on the machine, or indeed you could test for and create the DSN on their machine in your VBA module routines on startup) followed by a VBA loop through the TableDefs collection deleting and reattaching the relevant tables on startup.
One technique you might wish to think about is storing a table of table names that you wish to attach from the server. (You could prefix that table 'Usys' as tables prefixed with this automatically inherit hidden status) You could then loop down that table in VBA on start up and run the following function against each name found. Yes I know your tables are attached already and all they need is refreshing but what if one becomes unattached for whatever reason ie some savvy user messing things up
Code:
Function AttachODBCTable(strLocalTableName As String, strRemoteTableName As String, strServer As String, strDatabase As String, Optional strUsername As String, Optional strPassword As String)
On Error GoTo AttachODBCTable_Err
Dim td As TableDef
Dim strConnect As String
For Each td In CurrentDb.TableDefs
If td.Name = strLocalTableName Then
CurrentDb.TableDefs.Delete strLocalTableName
End If
Next
If Len(strUsername) = 0 Then
'//Use trusted authentication if strUsername is not supplied.
strConnect = "ODBC;DRIVER=SQL Server;SERVER=" & strServer & ";DATABASE=" & strDatabase & ";Trusted_Connection=Yes"
Else
'//This will save the username and the password with the linked table information.
strConnect = "ODBC;DRIVER=SQL Server;SERVER=" & strServer & ";DATABASE=" & strDatabase & ";UID=" & strUsername & ";PWD=" & strPassword
End If
Set td = CurrentDb.CreateTableDef(strLocalTableName, dbAttachSavePWD, strRemoteTableName, strConnect)
CurrentDb.TableDefs.Append td
AttachODBCTable = True
Exit Function
AttachODBCTable_Err:
AttachODBCTable = False
MsgBox "AttachODBCTable encountered an unexpected error: " & Err.Description, "System Message"
End Function