Hi,

Please help to decide the problem:
I am programming Access interface to MySQL-Database through ODBC. I need use both "Queries to server" and some special queries, made via ADO.
All "Queries to server" have default DSN-string which has all ODBC-DSN-data but not contain user-specific info such as UID and PWD (this is multy-user application).
When I call any of this "Queries to server" ODBC authentification window is pop up. After right Login and Password input other connected objects ("Queries to server", "Connected tables") with the same DSN does not require login and password input and use the same connection. Similar picture is when I connect using DAO.- All connected objects use DAO connection string.
 But in ADO case "Queries to server" use own default DSN string and ignore ADO ConnectionString string. As result, "Queries to server" produces ODBC-connection error, becouse they does not contain UID and PWD into default DSN string.

My question is:

 How to force "Queries to server" to use ADO Connection string instead own default DSN-string.

 Forward thank you to any help.:)

Remarks: "Queries to server" is English-Russian-English translation of "Pass-through Queries", "Connected tables" => "Linked tables"

Impossible, SourceConnectStr property is decision but I have a question:
What object property SourceConnectStr of?

Hi,

   Let me try to answer some of your questions:

1. To use the DSN connection, you have to state it in your connection and recordset instantiation, like this:

'----------
Set cnn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cnn.Open "DSN=YourDSNName;"
'----------

2. To pass the UID and password, you need to add them to this line:

'----------
Set cnn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cnn.Open "DSN=YourDSNName;", strUID, strPassword
'----------

Now, where you gather the username and password is up to you. I have a script that I use this on, and I just add the values in plain text:

'----------
Dim strUID As String
Dim strPassword As String

strUID = "My User ID"
strPassword = "Password"

'----------

I am not sure how you can get this info, other than prompting the user, or putting it into the plain text of the code.

If you can get your user's uid (like their computer login name) and then prompt them for their password when they open the database, that wouldn't be so bad.

If the UID and password for the MySQL server are different than the user's credentials (since you didn't set up an account for each user on the server, but are using a generic login for each user [bad practice]) then I would do the following:

Place some hidden data field on the main form that opens when the user opens that database. This will prompt the user for the userid and password for the MySQL account. It will also look like database security. Anyway, they will only be prompted for the UID and password when they first open the database, which is not unusual. I have done this.

You may want to go to your DSN registry entry and change the last logged in user to the generic userID they need to log into the server. This will make sure that when each user logs in, they will only see the generic UID, and not some administrator's UID. You can do this in the code for the On Open event of the main form, like this...

'----------
Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.RegWrite "HKLM\Software\ODBC\ODBC.INI\YourDSNName\LastUser" , "GenericUID"
'----------

If you are using Access 2003 in sandbox mode, this may not work.


HTH



mmcdonal

Thank You for answer!

This night I found that looking for.
Some later I'll publish my result.
In Russian it is on the http://www.it-ru.de/forum/viewtopic.php?p=923198#923198