Solved...
Code:
Function funRefreshODBC(strHSLCUser As String)
On Error GoTo ErrorHandler
Dim DAOdb As DAO.Database
Dim tblDef As DAO.TableDef
Dim rs As DAO.Recordset
Dim strConnectOld As String, mySQL As String, strUserID As String, strPass As String
Set DAOdb = CurrentDb
mySQL = "SELECT * FROM tblODBCConnections WHERE [ODBC_Database] = 'SAPRD';"
Set rs = CurrentDb.OpenRecordset(mySQL)
rs.MoveLast
rs.MoveFirst
strUserID = DLookup("UserSAPRD", "tblUsers", "[UserID] = '" & strHSLCUser & "'")
strPass = DLookup("UserSAPRDPass", "tblUsers", "[UserID] = '" & strHSLCUser & "'")
Do While Not rs.EOF
Set tblDef = DAOdb.TableDefs(rs![TableName])
tblDef.Connect = "ODBC; DSN=" & rs![ODBC_DSN] & "; DBQ=SAPRD; UID=" & strUserID & "; PWD=" & strPass
tblDef.RefreshLink
rs.MoveNext
Loop
rs.Close
mySQL = "SELECT * FROM tblODBCConnections WHERE [ODBC_Database] = 'FDOPRD';"
Set rs = CurrentDb.OpenRecordset(mySQL)
rs.MoveLast
rs.MoveFirst
strUserID = DLookup("UserFDOPRD", "tblUsers", "[UserID] = '" & strHSLCUser & "'")
strPass = DLookup("UserFDOPRDPass", "tblUsers", "[UserID] = '" & strHSLCUser & "'")
Do While Not rs.EOF
Set tblDef = DAOdb.TableDefs(rs![TableName])
tblDef.Connect = "ODBC; DSN=" & rs![ODBC_DSN] & "; DBQ=FDOPRD; UID=" & strUserID & "; PWD=" & strPass
tblDef.RefreshLink
rs.MoveNext
Loop
rs.Close
ResumeFunction:
DoCmd.SetWarnings True
DoCmd.Hourglass False
Exit Function
ErrorHandler:
MsgBox Err.Number & " - " & Err.Description, vbCritical, "Critical Error"
GoTo ResumeFunction
End Function