Scripting action queries

[scgarris]

Is there a way in Access to script a series of update queries in Access
without using macros and without having to define each UPDATE statement as a
separate, named query.

I have seen a VBA workaround described, but this seems to require defining
each UPDATE statement separately. Running this as an SQL script would be a
simple matter in most SQL implementations, is there really no way to automate
these queries in Access without adding the additional layer of VBA code and
naming each discrete UPDATE step?

Can CurrentDB.Execute be used with an UPDATE statement or does it have to
call a query by name?

If the VBA can only call named queries, is there a way to define and name
the queries in code rather than relying on the Access GUI?

 

[Allen Browne]

Yes: you can create SQL strings in VBA, and execute them.

For an example, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

It does mean building the entire SQL string. If most of it is the same, you
can use constants to hold the unchanging text, so you only have to build the
WHERE clause (for example.)

If you want to use a saved query with parameters, you can exeucte the
QueryDef after supplying a value for each parameter.