DSN UID and PWD

[rookery]

Please see the link below.

Not getting much joy in the SQL forum....

thread183-927315

 

[rsinj]

one simple way I can think of is to set environment variables for what you want to send in to the Access app. set up environment variables for the UID/PWD, then when Access starts up, let your code read the environment variables.

this is what a web server (like Apache) does on Windows when you want to have it execute a CGI program. it sets up the query string and web server parameters as environment variables prior to launching your program. the program then has access to them by reading the environment variables.

 

[rsinj]

Having re-read the other thread a little, I guess I didn't catch that you wanted to read the UID and PWD from a DSN and then send it to another Access front end.

Let me ask - why would you want to read the UID and PWD out of the DSN and not just use the DSN itself in the Access front end? You don't need to know the UID/PWD if you connect with the DSN. Am I missing something else here?

 

[rookery]

Thanks for your reply rsinj.

I need the UID/PWD from the DSN to use within the connection string. In my experience when linking SQL tables into an Access FE via code, you must specify a connection string explicitly referencing the UID/PWD.

It is not enough to simply specify the DSN. This is with SQL security implemented. It is not the case when using NT authentication.

If you know of another easier way I'm willing to stand corrected....

 

[rsinj]

I see now.

Are you able to link one table from the target database in your Access DB/App? Access gives you an option with linked tables to store the UID/PWD with it. I am certain you could then read the properties off the linked table at run time and use that connection string directly or extract the UID/PWD from it.

 

[rookery]

That sounds interesting.

I've looked in Linked Table Manager and cant find the "Save UID/PWD" option though? Would I pick up this table property in code?

 

[rsinj]

if you use File->Get External Data->Link Tables, after you identify the DSN/ODBC data source, when it provides the popup with the list of tables you can choose from, on the lower right of the popup, there is a checkbox for Save Password.

after linking the table, if you run the mouse over the linked table, it will display the connection string. I see the UID displayed in the connection string, but not the PWD. but, since it was saved with the linked table (if you double-clicked on the table, it would open up in Access without prompting for UID/PWD), I've got to believe there's a way to get at it.

 

[FancyPrairie]

You might try using a udl file. In windows explorer, in the folder you want to place the file, right-click and select "New Text Document". Rename the text document to something like MySqlConnection.UDL. Now double click on the file and Windows ODBC connection wizard will step you thru the process of creating the connection (including UID and Pwd).

Now use the File method of your connection string and have it point to your UDL file.

 

[rookery]

Thanks Prairie but I've made the parameters user-defined via a Form and then store the values as variables.

Slightly more straight forward!