When we released updated access database which were linked to an sql source the deployment requires re-connection to the client environment ; We would need to ensure the client PC had the DSN setup and tested, to simplify the process we call this DSN-less function. The program tests the connection when opening and advises the user if a re-connection is required.
I used the Microsoft code and a listing of tables in the database which were SQL tables and passed the parameters to the function.
DSNless Connection Function
Function refreshConnection(tmpServer As String, tmpDatabase As String, tmpUser As String, tmpPassword As String, tmpFiles As String)
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("Select * from tblLinkedSQLSOURCE where location=" & Chr(34) & tmpFiles & Chr(34), dbOpenDynaset, dbSeeChanges)
If rst.RecordCount > 0 Then
rst.MoveFirst
Do While Not rst.EOF
Form_frmConnect.lblStatus.Caption = Left("Connecting " & rst!Table & " at " & Now() & vbCrLf & Form_frmConnect.lblStatus.Caption & "", 1000) & ".."
DoEvents
Call AttachDSNLessTable(rst!Table, rst!File, tmpServer, tmpDatabase, tmpUser, tmpPassword)
rst.MoveNext
Loop
Else
MsgBox "No files in that list?"
End If
Set rst = Nothing
End Function

This is the function – which was based on this MS Solution
'//Name : AttachDSNLessTable
'//Purpose : Create a linked table to SQL Server without using a DSN
'//Parameters
'// stLocalTableName: Name of the table that you are creating in the current database
'// stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'// stServer: Name of the SQL Server that you are linking to
'// stDatabase: Name of the SQL Server database that you are linking to
'// stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'// stPassword: SQL Server user password
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
For Each td In CurrentDb.TableDefs
If td.Name = stLocalTableName Then
CurrentDb.TableDefs.Delete stLocalTableName
End If
Next
If Len(stUsername) = 0 Then
'//Use trusted authentication if stUsername is not supplied.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
Else
'//WARNING: This will save the username and the password with the linked table information.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
End If
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
AttachDSNLessTable = True
Exit Function
AttachDSNLessTable_Err:
AttachDSNLessTable = False
MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description
If MsgBox("Close Database", vbYesNo) = vbYes Then
DoCmd.Quit
End If
End Function
Links to other access code samples