VBA + Threads in MS Access

Question

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.

Solution 1

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

Solution 2

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.

Solution 3

Good question, but i think it can't be done.

Solution 4

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.

View more solutions

Comments

[Joe]

+1: correct. You can write a multithreaded component (or use an existing one) and call it from VBA, but VBA is not itself multithreaded.

[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]

That's not a good solution, it's bad, really bad

[Onkelborg]

No, it's executed on the same thread

[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