Solved

MSAccess Query stalls

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

920 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

16 Experts available now in Live!

Get 1:1 Help Now