Solved

MSAccess Query stalls

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

830 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