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
834 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
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

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

Title # Comments Views Activity
Which version of Microsoft Project does my client need? 3 78
Office 2013 constant OST corruption 22 129
Need a poor man's PowerPoint 5 75
Surface Book vs Surface Pro 4 57
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

863 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

28 Experts available now in Live!

Get 1:1 Help Now