?
Solved

How to force MS Access to WAIT for an append (or any) query to finish, before it moves on to next statement?

Posted on 2013-06-15
3
Medium Priority
?
1,008 Views
1 Endorsement
Last Modified: 2013-06-25
Using a SQL-Server back-end, it appears that a long-running union-query is 'kicked off' in a macro (also tried it in a function) and then execution immediately proceeds to the next line of the macro (or function).
Is there a way to make execution WAIT for completion of each query, before continuing on to execute the next?

For years, I've always assumed that a macro with OpenQuery actions just naturally waited until each query is completed before moving on....

Probably something simple.  Any thoughts?
Thanks in advance
1
Comment
Question by:bcreen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39250761
Actually, that's the first I heard of something like this.  Are you certain this is indeed happening?  Have you tried adding some debug.print statements with time stamps and record counts?
0
 

Accepted Solution

by:
bcreen earned 0 total points
ID: 39263084
I resolved this myself by changing docmd.openquery.... statements to dbs.execute .... statements.
Now, how in the world do I DELETE this question?
0
 

Author Closing Comment

by:bcreen
ID: 39274169
CurrentDatabase.Execute SQL, dbFailOnError statements -- against a SQL-server backend database -- complete the SQL statement BEFORE moving on to the next statement in VBA code or in a macro of multiple queries to be executed.

I had formerly macros and VBA code that had a series of queries that were being executed by DoCmd.OpenQuery (SQL, dbOpenForwardOnly) statements.

Turns out these statements execute sequentially, but often before the prior query has finished.... thus causing havoc, when one has "assumed" they would each FINISH before the next one started!  Especially true for long-running Union queries....
1

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

752 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