How to force MS Access to WAIT for an append (or any) query to finish, before it moves on to next statement?
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
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
IrogSinta
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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....
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....