multithread / asynchronous code

[J LaChance]

What is the easiest way (if there is one) to run multiple procedures in vba
simultaneously (am running on Access 2002(xp)). I have separate independent
procedures that are time consuming and would like to run them all at the same
time.

I can open 5 copies of the database and run them individually with no
conflicting problems, but that is, obviously, sub-optimal. Is there a way to
open a db and run a specified procedure from a .bat file, in which case, i
can use the shell command which is asynchronous, but I don't think you can.

My initial thought is to :
->create a table with the the procedure names to run
->loop through procedures
->shell blah.bat to run batch file which opens an access db (may have to
make multiple copies)
->autoexec on db selects first procedure in table which hasn't been run,
updates a flag in the table to state that it's running, and then runs the
procedure
->end loop

Concerns would be how this would perform with a dual processor machine if i
had 4 or 5 instances of access running code and how will i know when they
have completed

Thanks for any enlightenment you can shed on the topic

 

[Guest]

I think your missing the real problem here. Making more
work load for the system never makes things faster.

I'm going to make the assumption that the procedures use
table data. If so then you gain little or nothing at all
as the file system is your bottle neck. If you have lots
of RAM you would be better to make a RAM disk and run the
backend from it as you would gain considerably in IO
traffic. Then look at optimising the procedure code to use
SQL more and recordset's less. If it's still taking time,
then relook at what you are trying to do, as usually it's
overly complicated for access.

If it's code that's taking a long time, then move it to VB
code as it'll be faster compiled.