Pass through query to SQL Server

G

Guest

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
 
G

Guest

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
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL Pass-through Queries and List Boxes 5
Pass Through Query - not returning rows 1
Question on Pass-through Query 4
Pass Through Queries (Access 2002) 2
MS Access Front End - SQL Server Backend 7
Pass Through Query to SQL Server with Form Parameters 3
Append Passthrough Query Results to Local Table 2
refresh pass-through query 5

Top