Prevent user from having to enter password each time a pass-thru query is run

[aytee111]

Issue: no saving of user and password information allowed.

I have found out how to save this information for regular queries until the database is closed, then the data is gone. Perfect! But the same process does not work with pass-thru queries, each time one is run it asks for user name and password. I could change the connection string to include it using VBA but when the database closes I would have to change them all back and this brings too many risks, like the database closing without running through this code.



Does anyone know a way to make this happen? (It is to SQL Server if that makes any difference)

[pbaldy]

I use Windows authentication, so haven't had to worry about this. I suppose you could use a global variable (in addition to the global constant I assume you use for the string). When the user logs in, you build a connection string with their credentials and store it in the variable. When calling your pass through queries, you can test the variable; if length greater than 0, use it, else use the constant. That makes a lot of assumptions, but perhaps it makes sense? Or I'm all wet...wouldn't be the first time.

[aytee111]

This is all rather new to me too! And thanks for responding.

The ODBC string is stored in the query itself and doesn't seem to save anywhere, even if you run the same query twice it asks for the data source, username and password all over again. Very irritating. I have a reports database and that is all the user does is run these queries. For regular queries I found out that by creating a no-name query right at the beginning the info is stored and re-used for all other queries. But pass-thru ignores that.

I wonder if I could use RunSQL instead, somehow have the info in a variable as you suggest and rewrite the connect string.

[pbaldy]

One of my assumptions was that you use a function to modify the SQL of pass-through queries. I do, and in the call to that I can also specify a connection string to override whatever the saved string is. The connection string you refer to is a property of the query, and can be modified via QueryDef.

[aytee111]

Are you saving the query when you modify the connection string? That is the problem, then anyone can open the database and run the queries unless you change them back. We are removing all passwords from inside all databases and to me saving the query with the password is too risky.

I did find this brilliant thread with lots of good information on pass-thru queries: http://www.dbforums.com/showthread.p...Queries-in-VBA

Thanks for getting this subject stirred up for me again!

[Micron]

Are you saving the query when you modify the connection string? That is the problem, then anyone can open the database and run the queries unless you change them back.

I am 99.9% certain that if you save a query def and give it no name, it only exists as long as the procedure is running or at worst, until the db closes. It would not rely on code to be destroyed. It cannot be seen in the nav pane. Thought that might help for next time.

Set qdef = db.CreateQueryDef("", svSql)

[aytee111]

That is exactly what the solution was, well done! I found some of the pass-thru queries had different ODBC connection strings in their properties, once I had made them all the same then your solution worked for them all.

Thank you both!