query takes forever to run

[Guest]

I have a query which has queries joined to each other and also some of these
queries are joined with tables. All tables used are linked to a sql server.
When I run the query at times 30 minutes or more is used. What causes this
to happen and how can it be resolved.

 

[John Vinson]

I have a query which has queries joined to each other and also some of these
queries are joined with tables. All tables used are linked to a sql server.
When I run the query at times 30 minutes or more is used. What causes this
to happen and how can it be resolved.

You might want to create a View or a Stored Procedure in SQL, optimize
it there, and link to that view from Access. This will let the SQL
engine do the hard work rather than bringing the indexes - or even the
entire tables - down to Access for the JET engine to handle.
Alternatively, write an optimized Pass-Through Query in SQL/Server's
dialect of SQL and have SQL execute it and return the results to
Access.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Thank you for the great idea. I have many views created and now when I try
to link to them via access they do not show in my list of options. Many
other views show but not the ones I created. I have rebooted and still the
same problem. How can I complete this process.

 

[John Vinson]

Thank you for the great idea. I have many views created and now when I try
to link to them via access they do not show in my list of options. Many
other views show but not the ones I created. I have rebooted and still the
same problem. How can I complete this process.

I'm sorry, I'm not experienced enough in SQL interfaces from Access to
say. Perhaps you should repost (maybe in
microsoft.public.access.adpsqlserver or
microsoft.public.access.odbcclientserver) so someone who knows can
help!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps