You could use DSN-less connections. The first code will attach one table:

Sub AttachOne(TableName As String)

Const my_connection = "ODBC;DRIVER=SQL Native Client;SERVER=my_server;UID=my_username;PWD=my_password;DATABASE=my_database;"

'

Dim db As DAO.Database, tdf As DAO.TableDef

'

Set db = CurrentDb

Set tdf = db.CreateTableDef

With tdf

.Name = TableName

.SourceTableName = TableName

.Connect = my_connection

.Attributes = .Attributes Or dbAttachSavePWD

End With

db.TableDefs.Append tdf

End Sub

...and this code will refresh all of the linked tables:

Sub RelinkAll()

Const my_connection = "ODBC;DRIVER=SQL Native Client;SERVER=my_server;UID=my_username;PWD=my_password;DATABASE=my_database;"

'

Dim db As DAO.Database, tdf As DAO.TableDef

'

Set db = CurrentDb

For Each tdf In db.TableDefs

If tdf.Connect > "" Then

tdf.Connect = my_connection

tdf.RefreshLink

End If

Next tdf

End Sub