Refresh ODBC Connection with new UserID and Password

[jgelpi16]

How can I refresh an ODBC connection with a new User ID and Password? I cannot delete the table and re-link it due to primary key requirements. Is there a way to simply refresh the tables programmatically and give the ODBC connection a new/different User ID and Password?

[jgelpi16]

Solved...

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