Solved

MSAccess Query stalls

Posted on 2014-11-18
4
240 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

816 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

10 Experts available now in Live!

Get 1:1 Help Now