Run Multiple Update Queries at Once

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 25 Update Queries with the same parameters. I would like to create a
script or stored proc? to run them all at once and then have the user respond
to a form, providing the parameters for the update queries and hit OK,
thereby running the script or stored proc.

Anyone know how to create a script or stored proc to run multiple update
queries?

Thanks!
 
Create a function the get the parameteres, and run all SQL

Function RunAllQueries(Param1 as long, param2 as string)

docmd.runsql "UPDATE MyTable1 SET Field1 = " & Param1 & " WHERE Field2= '" &
Param2 & "'"
docmd.runsql "UPDATE MyTable2 SET Field1 = " & Param1 & " WHERE Field2= '" &
Param2 & "'"
docmd.runsql "UPDATE MyTable3 SET Field1 = " & Param1 & " WHERE Field2= '" &
Param2 & "'"

End function

On the on click event of the button run the function sending parameters from
the form

RunAllQueries me.Field1, me.Field2
 
Thanks for your response. I see how your suggestion would work. However, I
want to execute some existing Update Queries within the function. Is that
possible? If so, could you help me out with some sample code?

Thanks!
 
Hi,


DoCmd.RunSQL can be used on any "action" query, that means UPDATE and DELETE
(and DDL statements). By comparison, it cannot run a "SELECT" query.



Hoping it may help,
Vanderghast, Access MVP
 
Michel,

If I use the DoCmd.RunSQL, how do I define the parameters for the Update
Query that I want to run?

Like this?

DoCmd.RunSQL UpdateQuery1 (Me![Summary Date:],Me![Summary Iteration:])

Thanks
 
Hi,


If the query parameter are with the syntax:

FORMS!FormName!ControlName

then DoCmd will automatically look at the said open form to get the
parameter value.


Otherwise, you have to open the querydef object:

================

Dim qdf As QueryDef
Set qdf=CurrentDb.QueryDefs( "QueryName" )

Dim param As DAO.Parameter
For each param in qdf.Parameters
param.value= ... ' eval(param.name)
Next param

qdf.execute
================


Finally, with Jet 4.0 and DAO, you can use a procedure syntax.

------------------------------
CurrentProject.Connection.Execute "CREATE PROCEDURE AppendInventory( PartNo
Integer, Quantity Integer) As INSERT INTO Inventory (PartID, Qty, DateStamp)
VALUES(PartNo, Quantity, Date() )"
------------------------------

and then, note the syntax:

---------------------------------
CurrentProject.Connection.Execute "EXEC AppendInventory 1 , 6"
-----------------------------------

where the parameters, 1 and 6, are supplied after the name.


In that last case, you can also create the query as the usual (DAO) way of
creating query, by supplying an explicit PARAMETERS line:

------------------------------
PARAMETERS PartNo Long, Quantity Long;
INSERT INTO Inventory(PartID, Qty, DateStamp)
VALUES (PartNo, Quantity, Date());
-------------------------------



That is equivalent to the ADO CREATE PROCEDURE illustrated above. It may
happen that Access 2000 do not show the query created through the CREATE
PROCEDURE statement, in the Queries tab of the database window, in the user
interface, but that has been fixed in Access 2003.

(technically, CREATE PROCEDURE does not belong to ADO, but most of Jet 4.0
extensions are only available through ADO, which exclude the query designer,
based on DAO)




Hoping it may help,
Vanderghast, Access MVP



jrtmax said:
Michel,

If I use the DoCmd.RunSQL, how do I define the parameters for the Update
Query that I want to run?

Like this?

DoCmd.RunSQL UpdateQuery1 (Me![Summary Date:],Me![Summary Iteration:])

Thanks

Michel Walsh said:
Hi,


DoCmd.RunSQL can be used on any "action" query, that means UPDATE and
DELETE
(and DDL statements). By comparison, it cannot run a "SELECT" query.



Hoping it may help,
Vanderghast, Access MVP
Click to expand...
Click to expand...
 
Back
Top