Trusted Connection in Pass-trough query...

[Mr. Smith]

Hi
I have a Access application, where I use pass-throug queries against an MS
SQL 2008 server to access data. The Access application is a multiuser
solution, and all users have a DSN named dsn_sqlsrv1 on their computer.

How can I make sure that this DSN is used in the pass-through query ODBC
Connect str, and set "trusted_connection=yes". You know, it fail when I skip
the UID:

This work in the ODBC Connect str. field
ODBC;DRIVER=SQL Server;SERVER=sqlsrv1;UID=myuser;
DATABASE=mydatabase;Trusted_Connection=Yes

This does not work in the ODBC Connect str. field
ODBC;DRIVER=SQL Server;SERVER=sqlsrv1;
DATABASE=mydatabase;Trusted_Connection=Yes

All I relly want to do is set DSN=dsn_sqlsrv1 in the ODBC Connection str.
field, in the pass-through query propertie, but that' ai'nt work'n.

I thought "trusted connection" ment hat the connection picked up the local
user account, without the user name beeing a parameter in the connection
string.

How can I make a "dynamic" connection string int the pass-throug query ODBC
Connect str. field, that works on different users?

Messy post perhaps, sorry


Any hints appreciated.
Mr. Smith

 

[Douglas J. Steele]

Try:

ODBC;Driver={SQL Server Native Client
10.0};Server=sqlsrv1;Database=mydatabase;Trusted_Connection=yes;

 

[Mr. Smith]

Nice Douglas!Thanks!
It worked....

If I still have your attention...
Is it possible to call a setconnectionstring function I have declared in a
VBA module in the ODBC Connection str. part of the pass-trough query? As in
=setconstring(). The function is like this...

Public Function setconstring()
Dim constring
constring =
"ODBC;DSN=my_dsn;SRVR=my_server;DATABASE=my_db;Trusted_Connection=yes;"
setconstring = constring
End Function

I try to set =setconstring() in the ODBC Connection str. field, but it fail.
Is it "long shot" to try this, or is it possible?

My main objective is to cut down to one connection string in the entire
application. Right now I have one in VBA dealing with all the VBA DB
scenarios, but each pass-throug query need to be set as you explained. I'm
swithicn between developing environment and production environment, and ned
to change the connection string each time....

Kind regards
Mr. Smith

 

[Douglas J. Steele]

It's not possible.

 

[Mr. Smith]

Ok, thanks for solving it for me anyway Douglas.

Kind regards
Mr. Smith