Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

MSAccess Query stalls

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
mruff
Asked:
mruff
  • 2
2 Solutions
 
Gustav BrockCIOCommented:
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
 
Kelvin SparksCommented:
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
 
mruffAuthor Commented:
Hi Kelvin,
THX Yes you were right removing the where clause slowed down the query performance dramatically.
0
 
mruffAuthor Commented:
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now