How can I create a process running on a separate thread in MS Access VBA? I would like to create a process that will just sit and wait for a message.
Add a comment
|
7 Answers
You might want to check out this workaround: http://www.excelhero.com/blog/2010/05/multi-threaded-vba.html
It is Excel, but it should be practically the same. It works by building VBScript "agents" and have them execute tasks.. Check the example, it is quite impressive
-Viggo
Comments
There is no way to do this directly in VBA itself. Here is a MSDN forum discussion talking about this in detail. Office never exposed any of the VBA extensions for multithreading.
However, you can do this by calling out to the Windows API, or creating your own COM object in VBA (written elsewhere) which performs the multithreaded calls for you. Just make sure to marshall everything back to the calling thread, somehow (probably polling against your COM object, or something similar).
Also, you may want to check out bendewey's link on COM threading, since it's very relevant to this.
Comments
Good question, but i think it can't be done.
1 Comment
to StackOverflow
+1: correct. You can write a multithreaded component (or use an existing one) and call it from VBA, but VBA is not itself multithreaded.
How about using something like ShellOpen() to start a shell script, e.g Visual Basic Script x times, which will do the job, communicating with the script through a file (and a pooling mechanism to detect when results arrive)? I think it is easier to do than writing a COM component. Also, VB Script is very similar to VBA. Bad sides are quite a few, though - writing and reading a file is more time consuming than sharing memory, pooling might make the VBA script seem unresponsive, etc.
Comments
The quickest way I can think of is to create a Form and put an existing or do-it-yourself control on it.
Comments
You can also use the DoEvents procedure which lets the system handle the events. Just call this sub from time to time and the main thread shoudn't freeze.
3 Comments
Onkelborg
That's not a good solution, it's bad, really bad
Ionut
And why is such a bad solution? I've used this approach in my script and it worked nice. I just called DoEvents at an interval of 1000 ms or one second.
Onkelborg
Well, it worked, but that's it. An interface that responds to messages once per second isn't responsive. And it's bad practice to have a program execute code just for polling. Pushing is better to let the program sleep when nothing happens. Other programs benefits from this cause less context switches are happening, and the power consumption goes down. If you really, really has to do polling, don't use the DoEvents-approach, use a timer at least, it won't mess up your message queue and make your application unresponsive
If MS Access VBA lets you use forms, drop a timer on a form and set the delay to a really low value, like 10 ms. Then place your code in the timer event function, and it will be executed in a separate thread.
3 Comments
Reed Copsey
Timers in forms in VBA happen on the main thread. The form uses windows messages to schedule, not a separate thread.
andrewrk
Reed, I'm pretty sure this is true for VB6. Is it different for VBA?
Onkelborg
No, it's executed on the same thread
