How do you handle passwords in pass-thru queries generically 1

[Pack10]

I use a lot of Pass-thru queries and have gotten around the every three month password change because I could get a tech to change it back to the original. That system no longer works, so I have to change the pwd all over and then redistribute the front-ends to the users....that is a PIA.
Is there a way to store the password in a table, and create a function and put the function in the ODBC property of the pass-thru?

 

[dhookom]

I use a function to update the connection properties of tables and pass-throughs. You can modify the code to pass in the password or grab it in the code.

Function RelinkODBC()
    Dim td As DAO.TableDef
    Dim qd As DAO.QueryDef
    Dim db As DAO.Database
    Dim strConnect As String
    strConnect = "ODBC;Driver={SQL Server};Server=/YourServerName/;" & _
        "Database=/YourDBName/;Uid=/YourUserName/;Pwd=/YourPassword"
    Set db = CurrentDb
    For Each td In db.TableDefs
        If Left(td.Name, 4) = "dbo_" Then
            If Left(td.Connect, 4) = "ODBC" Then
                Debug.Print td.Name
                td.Connect = strConnect
                td.Attributes = DB_ATTACHSAVEPWD
                td.RefreshLink
            End If
        End If
    Next
    For Each qd In db.QueryDefs
        If Left(qd.Connect, 4) = "ODBC" Then
            Debug.Print qd.Name
            qd.Connect = strConnect
        End If
    Next
    Set td = Nothing
    Set qd = Nothing
    Set db = Nothing
End Function

Duane
Hook'D on Access
MS Access MVP

 

[Pack10]

Thanks Duane, I am almost there. Your code is excellent, I am storing the password in a table and reading it in to a variable named "pwd"....
However, in the ODBC connect string in the passthru's the pwd is being stored in the string, not the actual password. How would I get the actual value of the string stored in the table into the connect string...
something in the strConnect i would presume.

 

[PHV]

Did you try to build the strConnect string on the fly, ie with the password value stored in the table ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Pack10]

yes, i did. I created a table, stored the password in it, and built a function using dlookup. I passed that function to the RelinkODBC function.
The function sees the value of the password, but the pwd is getting embedded in the pass-thru connect string.

 

[Andrzejek]

" the pwd is getting embedded in the pass-thru connect string"
Isn't that what you wanted?

If you would show your code.....

Have fun.

---- Andy