Pass through query to SQL Server

[Jason Houchen]

Are there any bugs/issues with using pass through queries in Access?

I'm using Access 2k and my database contains a number of pass through
queries that connect to a SQL Server 2000 database. I haven't had any real
problems with these queries until today. For one of the queries, I need to
increase the timeout to 20 minutes to avoid getting a time out error.
However, if I execute the SQL in query analyzer, it takes just over 1 minute.
The query is fairly complex and involves unioning together 3 seperate
queries. Is this what's causing the problem?

This problem is also being experienced by other users on other machines.

Thanks,

Jason

 

[Alex Dybenko]

Hi Jason,
never had such problems. perhaps you are using different connection option -
user id for example?

you can also consider moving this query to view, function or SP

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Jason Houchen]

Thanks for the reply, Alex.

I've just tried to run the problem query in Access today, and it ran in just
over 90 seconds!! I mentioned the problem to a colleague and he seemed to
think that it could be the way Access queries the database. He thought that
Access would lock the tables used in the result set and wouldn't release them
until it had completed the query. Do you - or anybody else - know if this is
the case? It still probably won't explain why it didn't work yesterday and
works today, though!!

I'll look into the other options you mentioned, but I'm interested as to why
I'm getting the problem.

Thanks again,

Jason

 

[Alex Dybenko]

Hi Jason,
AFAIK - Access do not lock SQL Server tables, it just pass sql to a server
and get result. you can examine this using SQL Server profiler.
But could be that some other process lock table, for example access form,
linked to it.
Furthermore - you can use "with (nolock)" hint to make sure that you do not
establish any lock

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com