When we are talking about Access databases, split or not, if Access is running the FE and the BE then NOTHING in Access itself including the VBA code is asynchronous - with the possibility of physical faults (i.e. power fail, device interrupt, device error, and memory management faults) leading to traps that can interrupt event code. (That's why you use Exit Sub for something that gets called, including Event code, but you use Resume Next or Resume
label-name after a trap. Two different call contexts.)
That is because Access, which is explicitly written single-threaded, can never make use of the multiple processors in a machine that has them. That fact was discussed in some MSDN articles, though it has been a while since I read that. Oh, the thread running Access might bounce from one CPU to another as part of the normal process scheduler functions, but NO PART of Access itself is capable of asynchronous operation except for the aforementioned hardware traps. It just isn't written that way. Even the VBA code is not really "running." It is being
interpreted synchronously because VBA is a PSEUDO-compiler. Note that a DLL written in a truly compiled language, even VB6, CAN go asynch - but that is not anything that MSACCESS.EXE handles.
The event code is synchronous because Access TESTS for each event in the main program and dispatches (calls via table) the appropriate event code. As the event occurs, Access tests whether the object in question has an Event Code pointer as part of its object properties sheet. The actual event occurs whether or not a routine has been declared. That is, the form opens even if no Form_Open event routine exists. Remember also that event codes CANNOT interrupt other event codes (again, with the hardware trap exception).
Where asynchronicity occurs is in the SQL engine that comes with Access. JET for older versions and ACE for the newer versions were indeed written with the ability to go multi-threaded. So once you leave the domain of Access and let SQL get executed by an SQL execution engine, there is where the issue becomes trickier to discuss.
According to the MSDN articles, JET and ACE have the ability to use multiple threads, though what I had read made it seem like only the cleanup might go asynch on you.
Gent, if you want to experiment on this, set up your test to run those two queries in-line but separate them with a DBEngine.Idle (and you'll have to look up the correct argument for this case, I'm posting from home and I don't keep Access open here like I used to at the office). See if the problem you described occurs when you force Access to wait for the underlying engine to go idle between successive queries.
Supplement: Use DBEngine.Idle dbRefreshCache
Also, if you use the DAO-based DAODB.Execute "query" command, you should ALWAYS include dbFailOnError as the second parameter, and you should be prepared to trap errors when the .Execute fails. The good news about the .Execute is that it is supposed to "roll back" when an error triggers that "fail" trap. I used that a lot with the SPAWAR NEDC NO database that was my main Access project. Worked like a champ.
From your first post:
Am I just getting lucky or am I correct in thinking the the query would execute completly before returning to the module that called it?
Not necessarily lucky or unlucky. The query will return results to its caller - but the moment it does, that caller (your separate subroutine) returns to the fully synchronous environment that is controlled by Access. To the best of my understanding when I read up on this, the window during which that asynchronicity exists is very short anyway. By forcing the code back into that single-thread environment, you give it time to "catch up" with the main thread.
