VBA run several Delete Quries (1 Viewer)

hankins0005 · Sep 13, 2017

I am in need of some code or module to run four Delete and Append queries. I cannot use a Macro as the OpenQuery will only work on cross tab and select. I am not sure where to go with this so any help guys would be good

plog · Sep 13, 2017

OpenQuery in a macro will execute DELETE and APPEND queries:

https://msdn.microsoft.com/en-us/library/bb177340(v=office.12).aspx

What makes you say that it doesn't? Getting an error message?

Lightwave · Sep 14, 2017

To run any created function ensure the the immediate window is open and type a ? and followed by the name of your function.

The generalised code is

Code:

Public Function RunQueries()

DoCmd.SetWarnings False
DoCmd.OpenQuery ("Query01")
DoCmd.OpenQuery ("Query02")
DoCmd.SetWarnings True
MsgBox "Finished"

End Function

Repeat the lines OpenQuery with your query names in the "" for each query that you want to run.

Same example with some pictures

Scripting Queries

Ranman256 · Sep 14, 2017

thats what macros are for.
put all your queries into a macro,
run the macro.

put the macro in the button click event.
(no programming needed)

MarkK · Sep 14, 2017

You can also write a very simple loop...

Code:

Private Sub cmdRunQueries_Click()
   Dim var

   For Each var In Split("Query1 Query2 SomeOtherQuery LastQuery")
      CurrentDb.QueryDefs(var).Execute dbFailOnError
   Next
End Sub

hth
Mark

Lightwave · Sep 14, 2017

Nice one Mark

and if you want to run thousands of queries you can place them in a table and then run the queries from the table in a loop. You will of course have to figure out how to programmatically create thousands of queries and why. (I have used this for improving poor addresses by matching them against a clean set of data made of queries self created.

Code:

Public Function RunQueriesFromTable(SQLSource As String)

DoCmd.SetWarnings False

Dim StartTime As Date
Dim EndTime As Date
Dim rstZ As DAO.Recordset
Dim strSQL As String

StartTime = Now()

Set rstZ = CurrentDb.OpenRecordset(SQLSource)

Do Until rstZ.EOF

strSQL = rstZ!SQL
DoCmd.RunSQL strSQL
rstZ.MoveNext

Loop

DoCmd.SetWarnings True

EndTime = Now()

MsgBox "Finished ALL SQL update queries! Process started at " & StartTime & " and finished at " & EndTime

End Function

isladogs · Sep 14, 2017

You can also write a very simple loop...

Hi Mark
Now that really was clever
I'd never have thought of doing that

NauticalGent · Sep 15, 2017

I was unaware that if you did not explicitly define a delimiter in the Split function, it would default to spaces. Additionally, I always ASSumed that the delimiter was NOT optional.

Live and learn... Thanks MarkK, nifty bit of code.

VBA run several Delete Quries (1 Viewer)

hankins0005

New member

plog

Banishment Pending

Lightwave

Ad astra

Ranman256

Well-known member

MarkK

bit cruncher

Lightwave

Ad astra

isladogs

MVP / VIP

NauticalGent

Ignore List Poster Boy

Similar threads

Users who are viewing this thread