By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,561 Members | 3,221 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,561 IT Pros & Developers. It's quick & easy.

Does VBA complete tasks asynchronously?

P: 579
Hi everybody,

[Access 2010]

I've seen conflicting answers online, so I thought I'd see what answers I would get here.

If I have a VBA module that has code that sets multiple variables to different QueryDefs, assigns SQL to the SQL property of the QueryDef, and either executes the QueryDef or exports the query using something like DoCmd.TransferSpreadsheet, will each line of code run and not progress to the next line until the task that was launched from the line of code is completely finished?

For instance, if I have the following code where one subroutine runs, then calls another subroutine, and both export a spreadsheet, will the code wait for the spreadsheet to export completely before going to the next line of code? This is a very basic example, so please ignore the fact that there's no reason to call the second subroutine that I have in my example. Just know that in my real project, I'm actually passing information to the second subroutine based on values that change in a For Loop in the first subroutine. I just didn't want to include hundreds of lines of code to get my point across and hopefully this still does that.

Expand|Select|Wrap|Line Numbers
  1. Public Sub RunFirstProcess()
  2.     Dim db AS DAO.Database
  3.     Dim qdfQueryOne AS DAO.QueryDef
  4.     Dim strQueryOne AS String
  6.     Set db = CurrentDb
  7.     Set qdfQueryOne = db.QueryDefs("qryOne")
  9.     'Change the SQL for the query
  10.     strQueryOne = "SELECT * FROM qryOne WHERE Code = 'A'"
  12.     qdfQueryOne.SQL = strQueryOne
  13.     qdfQueryOne.Close
  15.     DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel12Xml, TableName:=qdfQueryOne.Name, FileName:="C:\Desktop\QueryOne.xlsx"
  17.     RunSecondProcess
  19.     Set qdfQueryOne = Nothing
  20.     Set db = Nothing
  21. End Sub
  23. Public Sub RunSecondProcess()
  24.     Dim dbs AS DAO.Database
  25.     Dim qdfQueryTwo AS DAO.QueryDef
  26.     Dim strQueryTwo AS String
  28.     Set dbs = CurrentDb
  29.     Set qdfQueryTwo = dbs.QueryDefs("qryTwo")
  31.     'Change the SQL for the query
  32.     strQueryTwo = "SELECT * FROM qryTwo WHERE Code = 'C'"
  34.     qdfQueryTwo.SQL = strQueryTwo
  35.     qdfQueryTwo.Close
  37.     DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel12Xml, TableName:=qdfQueryTwo.Name, FileName:="C:\Desktop\QueryTwo.xlsx"
  39.     Set qdfQueryTwo = Nothing
  40.     Set db = Nothing
  42. End Sub
Will the export of query #2 wait until the export of query #1 has completed? I want to make sure that everything is launched and completed in sequence (not asynchronously), so if that's not what's happening, how can I make that happen?

Sep 10 '12 #1
Share this Question
Share on Google+
6 Replies

Expert Mod 2.5K+
P: 3,284

As far as I know, all code executes sequentially. For example, you can't run a query "in the background", because that query monopolizes the tasks until it is finished. So, to answer your quetion, if you called a subroutine, and modified the value of a global variable using that subroutine, the next time you referred to that variable, it would be based on that latest modification.

I have seen this all the time, because when debugging, as you line through code, it may take you various places based on the various subs you call or refer to in your queries.

Hope this helps and answers your question satisfactorily....
Sep 10 '12 #2

Expert Mod 5K+
P: 5,397
It depends upon what you are doing...

In the old days of BASIC you had the main code and then subroutines... in a very simplistic view... now all we have are subroutines. Thus, if you have one sub call another then it is the same as the old school GOSUB 100...some…code...RETURN... one line processes at a time.

Now... if you if you shell out a process, instance a new form, issue a doevents, open a an excelworkbook, etc... you need to check that those actions have completed before the next line of the code runs if that code depends on the object (for shelled event: )

Infact, I have a not-in-list event that gave me heartburn because I didn't open the form to enter new events as modal... the form opened and the not-in-list event code continued to run (v2003) while the form was opening! I didn't want the form to open modal because I needed the user to be able to look at another opened form. Surprise!

So now we have the best and worse of both worlds.

In the example you've given, I think you can specify the async ( ). HOWEVER, I would not be surprised to find that the second call is attempting to run as I believe that the transfersheet method shells the event over to excel. Second code block in this link shows a clear async query run:

Sep 10 '12 #3

P: 579
I'm just working with modules as a way to iterate through different collections of queries (basically to change the source for the query) and export some of them to Excel.

I think you're right about the TransferSpreadsheet method shelling the event to Excel, which is fine so long as the shelled event isn't affected by the VBA code going to the next line. The next line is usually the next iteration in the For..Loop, which is usually to change the source for the queries.

Is the shelled event affected by the code progressing? It's possible that, due to the amount of data being exported, multiple spreadsheets could be processing at the same time while the queries are changing in the code.

This is somewhat off topic, but what exactly does DoEvents do? I understand that it passes the VBA code over to the operating system for processing, but I'm not entirely clear on the advantage/disadvantage of doing this to enable asynchronous execution of code or not, or does it have nothing to do with that at all?
Sep 10 '12 #4

Expert Mod 5K+
P: 5,397
A tad out of my depth for how the iterations are going to effect the transfersheet method... I would think that would have to do with how much the OS could handle - in truth, I don't have a answer. It may very well be that the database engine will que the queries.

As for going OT... let's bring it back and answer at the same time... You're using a for-loop to kick your queries thru, by using the doevents within the loop, you will allow the OS to handle any of the multitasking and keyboard events that might be pending. soo.... you might get the effect of an async execution and in anycase allow the EXCEL object some processor time.

Sep 10 '12 #5

Expert Mod 100+
P: 2,321
DoEvents is not something I use much, in fact looking through my last 5 projects I only find I have used it once, in a function to scrape HTML off a website, in which I use a IE object, and I wait (using a loop and DoEvents) for the IE to finish loading the page I wish to retrieve.

I do believe that in your case the code will wait for the query to finish processing before moving on. If you are seeing otherwise it may be that you need to force a save of the worksheet, before using said worksheet again.

I think that Shell and other outside applications might run their own life, but I do think that office applications will wait for your code to finish.
Sep 11 '12 #6

P: 374
As mentioned I would take a look at using DoEvents when running anything outside of access is being run. I have seen the code execute many times in a non sequential way unless I use DoEvents which in my eyes forces the execution of the code to go the way you want.

I had the issue quite recently when displaying a "loading" form whilst running a pass through query. The form would simply not open or close at the correct times (VBA executed the open/close all at once after the query had run even though the query was nested between a and a docmd.close).
Oct 4 '12 #7

Post your reply

Sign in to post your reply or Sign up for a free account.