Access sql pass through query change user

Question

I inherited several Access databases when my coworker died suddenly. These contain sql pass through queries that I am unable to run as they are looking for his odbc connection with his stored password. How do I change the connection to me so that the queries will run?

Answer 1

In the query Properties window, there should be a field called something like "ODBC Connect Str". The value will look like this:

ODBC;DSN=DatabaseName;UID=HisUserID;PWD=HisPassword;maybeOtherStuffHere

Just change the UID and PWD values to yours. You will have to do this for each query.

It's also a good idea to have a service account for things like this so that you don't run into issues when someone leaves or is otherwise unreachable.

Answer 2

Public Sub FixOwner()
    Dim oTable As TableDef
    For Each oTable In CurrentDb.TableDefs
        If oTable.Connect <> "" And InStr(oTable.Connect, "hisUserID") > 0 Then
            oTable.Connect = Replace(oTable.Connect, "HisUserID", "YourUserID")
            oTable.Connect = Replace(oTable.Connect, "HisPassword", "YourPassword")
        End If
    Next

    Dim oQuery As QueryDef
    For Each oQuery In CurrentDb.QueryDefs
        If oQuery.Connect <> "" And InStr(oQuery.Connect, "hisUserID") > 0 Then
            oQuery.Connect = Replace(oQuery.Connect, "HisUserID", "YourUserID")
            oQuery.Connect = Replace(oQuery.Connect, "HisPassword", "YourPassword")
        End If
    Next
End Sub