Running multiple SQL queries

  • Thread starter Thread starter Bill via AccessMonster.com
  • Start date Start date
B

Bill via AccessMonster.com

Hello,
I was wondering if any one knew of a way to run multiple SQL queries from
different modules either one after another or all at the same time. I have 7
modules that all contain SQL queries in IF.. Then.. IFelse statements. I
need a way to have them all run. I will be using the RunCode action in macro
to run these. Thanks in advance.

Bill
 
Hi,


Since VBA has only one thread of execution, you cannot start concurrent
thread, directly. On the other hand, if the query return records, you can
try opening seven forms, each with a record source set to the query. SOME
asynchronous action could kick in.


If the queries are ACTION queries, it may be preferable to execute them one
after the other, so they minimize the possibility that they DEAD LOCK each
other.



Hoping it may help,
Vanderghast, Access MVP
 
Thanks Michel for the response.
They are all action queries and running one after another would be fine but
what would be a good method to this? I need to make sure that all of them
get run. They are appending to many different tables and I want to make sure
that all posible fields get appended and appended correctly. Does anyone
know of a way to set them off one after another? Thanks.
 
Hi,


One way to do it is to start a transaction, Execute them one after the
other, and if no error is reported, commit, else, rollback.

Untested:


------------------------
Public Sub DoSequence()

DBEngine.Workspace(0).BeginTrans

On Error Goto Fallback
DBEngine.Workspace(0).Databases(0).Execute sql1, dbFailOnError
DBEngine.Workspace(0).Databases(0).Execute sql2, dbFailOnError
...
DBEngine.Workspace(0).Databases(0).Execute sqlN, dbFailOnError
DBEngine.Workspace(0).CommitTrans ' no error, make it sticky
Exit Sub

FallBack:
DBEngine.Workspace(0).Rollback ' some error, undo whatever was 'done'
up to now

End Sub
-----------------------------


I personally create a workspace when I imply a transaction, but I think
we can also live with the default workspace, as done here up.


Hoping it may help,
Vanderghast, Access MVP
 
Thanks Michel for the response.
They are all action queries and running one after another would be fine but
what would be a good method to this? I need to make sure that all of them
get run. They are appending to many different tables and I want to make sure
that all posible fields get appended and appended correctly. Does anyone
know of a way to set them off one after another? Thanks.
Click to expand...

Try this:
http://www.mvps.org/access/queries/qry0014.htm

HTH,
RD
 
Back
Top