Solved

MSAccess Query stalls

Posted on 2014-11-18
4
230 Views
Last Modified: 2014-11-19
Dear Experts.
I have a MSAccess query which runs well. Now I have to apply a minor change - remove a where clause.
When I change the query and save it, it does NOT execute anymore. When I run the query, MSAccess stalls with the hour glass cursor and does NOT execute the query, I have to kill MSAccess via the Task Manager.
The query is based on all local tables. The amount of data is low, the main table has 3000 entries. However the query has about 30 left joins to all local tables.
I tried: copy the query and paste, direclty via copy/pase ->did not solve the issue.
Copy the SQL code, made a new query, pasted the SQL code. The query is valid, can be viewed in design mode ->still not executing.
COmpacted/repaired the DB
Nothing solved the issue, I have no further ideas.
Any help is VERY appreicated
0
Comment
Question by:mruff
  • 2
4 Comments
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 40451718
30 joins is a lot.
Try splitting it into several subqueries.

For a start, remove half of the joins. If that works, test with the other half. If that also works, combine these two queries.
If it doesn't work, continue splitting or remove joins until success. Then rebuild the full query.

/gustav
0
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 250 total points
ID: 40451719
Left joins are very slow (compared to inner joins). By removing the where clause, you are effectively asking the query to return all data in the database. Although you say it stalls, if you left it alone, I suspect it would finally return the data. I have seen similar cases, where the data "appeared" after 4 hour hours. You will see a message at the top says Access has stopped responding - ignore that - it just means it has taken more than 30 seconds!

Set it going overnight (or some other time when you don't need the PC for anything else) and see what happens - you'll either get the results or eventually get an error message. Either way, you'll know what you're dealing with.

Kelvin
0
 

Author Comment

by:mruff
ID: 40451746
Hi Kelvin,
THX Yes you were right removing the where clause slowed down the query performance dramatically.
0
 

Author Comment

by:mruff
ID: 40451748
Dear expoerts,
@Gustav, THX for your answer. Sorry noticed it too late, I asked the moderator so split the points between you and Kelvin.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now