12

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.

shruti1810
  • 3,920
  • 2
  • 16
  • 28
Curtis Inderwiesche
  • 4,940
  • 19
  • 60
  • 82

7 Answers7

7

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

ViggoV
  • 2,133
  • 2
  • 20
  • 22
5

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.

Reed Copsey
  • 554,122
  • 78
  • 1,158
  • 1,373
3

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

Mark Nold
  • 5,638
  • 7
  • 31
  • 33
  • 2
    +1: correct. You can write a multithreaded component (or use an existing one) and call it from VBA, but VBA is not itself multithreaded. – Joe Apr 11 '09 at 20:41
1

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.

MichaelS
  • 271
  • 2
  • 4
0

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.

Ionut
  • 11
  • 4
    That's not a good solution, it's bad, really bad – Onkelborg Jul 16 '12 at 06:42
  • 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. – Ionut Jul 16 '12 at 06:53
  • 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 – Onkelborg Jul 16 '12 at 07:18
0

The quickest way I can think of is to create a Form and put an existing or do-it-yourself control on it.

dkretz
  • 37,399
  • 13
  • 80
  • 138
-1

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.

andrewrk
  • 30,272
  • 27
  • 92
  • 113