about multi threading with vba (1 Viewer)

Hello all,
I'm thinking that multi threading of different functions could accelerate my process. I've google that, and found many controversial information.

It is a good ideia to use multi threading in access?
If yes, how can i do that?

Thanks !

I don't think access will allow it.

I found some sites that said yes, but i didn't understand how.
And about CPU? can I improve the CPU usage? seems that it is only using one processor

Access itself will not multi-thread your VBA code. To multi-thread, you have to be able to declare thread "forks" and "rejoins" - i.e. places where a separate thread could start or end. Since VBA code is interpretive and the interpreter is not multi-threaded, I think the answer is a definitive NO for VBA multi-threading.

However, we cannot forget that the main drivers of MSAccess(.exe) talk to a compiled database engine that DOES sometimes multi-thread things like read-ahead or write-behind functions, and that can perform queries and manipulate buffers at the same time. Which is why there are DBEngine methods to help you force those threads to synchronize.

If you REALLY got a bit twitchy on this subject, there is still a way but (a) you need to be careful, (b) you need a "hot" machine, and (c) you will need extra memory depending on how you would approach this.

At least in theory, you can write macros that perform a RunCode action. So if you could define your threads in routines to be run via a Macro, there is a chance for you to create a shelled copy of MSAccess running only that macro via the command-line /X:macroname option (and exiting on its own). That shelled copy would be ANOTHER copy of MSAccess running on the same machine, which is allowed (I think). But I've never tried it and cannot remember if that results in two instantiations of Access or only one that time-shares between the two databases.

Note, for example, that you explicitly CANNOT do this type of thing with Outlook. If you have a copy of Outlook already open, you MUST use the extant copy because it has some kind of interlock that blocks you from multi-threading in that case. But I can't speak for whether Access does this. All I can suggest is to try it, run Task Manager to see if you get multiple copies of MSAccess.EXE in memory or not, and thus find out if it is possible to do what you wanted.

Thanks, The_Doc_Man , was really interesting your answer and now i understood.

The_Doc_Man said:

At least in theory, you can write macros that perform a RunCode action. So if you could define your threads in routines to be run via a Macro, there is a chance for you to create a shelled copy of MSAccess running only that macro via the command-line /X:macroname option (and exiting on its own). That shelled copy would be ANOTHER copy of MSAccess running on the same machine, which is allowed (I think). But I've never tried it and cannot remember if that results in two instantiations of Access or only one that time-shares between the two databases.

Click to expand...

This is exactly the kind of thing I have done in the past with "non performing processes", where the bottle neck was in a third party system.
The memory/cpu usage localy was negligible, and access was waiting on the third party system for a response. Ended up starting 4 shadow databases (as I called them) that each took care of a part of all the records that needed processing.

Effectively splitting 100k records into 5 separate processes, running the "master" from record 1 to 20k then taking the data as processed from shadow 1, which did process 20.001 thru to 39k making sure it stops before the master comes to fetch data by having the master check for the ldb file on the shadow.
Each next shadow does process the full 20k which should (nearly) finish before the master comes to "visit" but always checking for the ldb and always double checking any "missed" records in the master process.
In my case roughly 5% could not be processed automatically at all, an additional 5% needed more complex decision making, the master was a little more elaborate in coding than the shadows, which meant that 10% was rechecked by the master which saved a lot on maintenance but still got things done A LOT faster.

In my case I didn't use any /x command line stuff, instead simply used a separate form to open on startup and have it auto run a procedure [on open]

Namliam, your solution works when you have a back-end that is some type of active SQL system (SQL server, MySQL, ORACLE, etc.) where you have code executing actively on the back end. Specifically for the case of SMB connections to a back-end access file (where the back-end is acting like a passive file server), this approach wouldn't work UNLESS you have code that gets stymied and, when it does, executes a DoEvents in the part where it is stymied. That would allow the Windows Scheduler to do something else. However, I still doubt whether a single memory instance of Access would EVER multi-thread in the sense that mfaqueiroz asked the question.

The access VBA interpreter is inherently single-threaded and the event management scheme is being managed by a single "chunk" of MSAccess.EXE code that, at most, intercepts events for each open form or report as those events occur. However, with the exception of network events and hardware-related events, ALL of those events are technically synchronous. The only way that event thread B can execute is if event thread A finishes (Exit Sub) or gets out of the way (DoEvents).