ODBC: Password

[asearle]

Hallo Everyone,

I have an MS-Access frontend that interacts with an Oracle database using mostly passthrough queries that are written and overwritten programmatically. These queries have the ODBC information filled out but no password information is included.

This is extremely comfortable (both for the developer and for the user) because once one query has been called up and MS-Access has (automatically) requested the user-id and password no futher action is required and all other queries use this information.

However, I have programmed some SQL calls (to the Oracle database) in VBA using ADO ...

sConn = "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=" & _
"(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)" & _
"(Host=mydatabase.com)(Port=15297))) " & _
"(CONNECT_DATA = (SID = MYSID)(SERVER = DEDICATED)));" & _
"User ID=johndoe;Password=secretpassword;"

... with the password being hard-coded in.

This is of course wrong and so I will be replacing the password with a variable.

What would be absolutely wonderful is if I could pick up the password variable that is generated/entered for the existing queries rather than have my own, second variable that the user has to enter.

Can anyone help me with this? Am I able to capture the password that is requested/entered for standard queries?

Many thanks for any tips on this topic.

Regards,
Alan Searle

[taxidev]

You can read the connect string on the querydef, extracting the UID and PWD values.
Simple code example:

Dim qDef As DAO.queryDef

Set qDef = CurrentDb.QueryDefs("qTest")
Debug.Print qDef.Connect

Hope this helps.

[asearle]

Yes, many thanks this is the property that I ended up using: In the end, I decided to prompt the user for his details when the application opens and then build the connect string myself (using the property you suggest).

This works fine and takes all uncertainty out of the matter.

Many thanks for the tip.

Regards,
Alan