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
811 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
  • 2
3 Comments
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 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