Other users can't execute my pass through queries

[gt]

Using Access 2003.

I have a Pass through query to a SQL server, using a File DSN - works fine
for me. However, other users on our local network cannot run the query - they
get an ODBC call failed error message. I tried copying the DSN to their
machines - still no luck.

However, if I create the same query in Excel, using the same DSN, those same
users can refresh it - so it would not appear to be a case of permissions ...
they don't even need the DSN on their box.

What's going on? The connection strings are identical. The query is identical.

Any help is greatly appreciated. Thanks, gt

 

[MGFoster]

Using Access 2003.

I have a Pass through query to a SQL server, using a File DSN - works fine
for me. However, other users on our local network cannot run the query - they
get an ODBC call failed error message. I tried copying the DSN to their
machines - still no luck.

However, if I create the same query in Excel, using the same DSN, those same
users can refresh it - so it would not appear to be a case of permissions ...
they don't even need the DSN on their box.

What's going on? The connection strings are identical. The query is identical.

Any help is greatly appreciated. Thanks, gt

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try a DSN-less connection.

Change the pass-thru query's ODBC Connect Str to something like this
(all one line):

ODBC;Driver={SQL Server};ServerName=YourServerName;
DatabaseName=YourDatabaseName;TrustedConnection=Yes

Change the server and database names to your server & database names.
Trusted Connection means SQL Server is using System Authentication.

Each PC will require an SQL Server ODBC driver on it - this is usually
automatically installed when Access is installed, or any Office
application.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSWfr6YechKqOuFEgEQIXTQCfeRU1UVeOUZ5MtzO4O7KNZmQ6STEAnAmM
3f8thJ0oD0kqjxI8rIRlG8R+
=VG++
-----END PGP SIGNATURE-----

 

[gt]

Thanks MGFoster I will give that a whirl today.

cheers,
gt

 

[gt]

Hi again -

I tried your suggestion, but when the query is executed, a SQL Server Login
dialog box is displayed, and it prompts me to select the server from a list.
Here's the ODBC connection string exactly as I have it specified:

ODBC;Driver={SQL Server};ServerName=s13021-d;
DatabaseName=RPTData_Prod;TrustedConnection=Yes

Since I've specified the server name, why is it prompting me to enter one?

Thanks - and thanks for your suggestion.