Does VBA complete tasks asynchronously?

[beacon]

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.

Public Sub RunFirstProcess()
    Dim db AS DAO.Database
    Dim qdfQueryOne AS DAO.QueryDef
    Dim strQueryOne AS String
 
    Set db = CurrentDb
    Set qdfQueryOne = db.QueryDefs("qryOne")
 
    'Change the SQL for the query
    strQueryOne = "SELECT * FROM qryOne WHERE Code = 'A'"
 
    qdfQueryOne.SQL = strQueryOne
    qdfQueryOne.Close
 
    DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel12Xml, TableName:=qdfQueryOne.Name, FileName:="C:\Desktop\QueryOne.xlsx"
 
    RunSecondProcess
 
    Set qdfQueryOne = Nothing
    Set db = Nothing
End Sub
 
Public Sub RunSecondProcess()
    Dim dbs AS DAO.Database
    Dim qdfQueryTwo AS DAO.QueryDef
    Dim strQueryTwo AS String
 
    Set dbs = CurrentDb
    Set qdfQueryTwo = dbs.QueryDefs("qryTwo")
 
    'Change the SQL for the query
    strQueryTwo = "SELECT * FROM qryTwo WHERE Code = 'C'"
 
    qdfQueryTwo.SQL = strQueryTwo
    qdfQueryTwo.Close
 
    DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel12Xml, TableName:=qdfQueryTwo.Name, FileName:="C:\Desktop\QueryTwo.xlsx"
 
    Set qdfQueryTwo = Nothing
    Set db = Nothing
 
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?

Thanks,
beacon

[twinnyfo]

Beacon,

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

[zmbd]

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: http://msdn.microsoft.com/en-us/libr...ffice.12).aspx )

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 ( http://msdn.microsoft.com/en-us/libr...ffice.12).aspx ). 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: http://msdn.microsoft.com/en-us/libr.../ff197799.aspx

-z

[beacon]

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...is this to enable asynchronous execution of code or not, or does it have nothing to do with that at all?

[zmbd]

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. http://office.microsoft.com/en-us/ac...001228827.aspx soo.... you might get the effect of an async execution and in anycase allow the EXCEL object some processor time.

-z

[TheSmileyCoder]

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.

[munkee]

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 docmd.open and a docmd.close).