Run a query using code.

  • Thread starter Thread starter Terry Thomas
  • Start date Start date
T

Terry Thomas

Hi I have two queries, one is an append query and the
other is a delete query.

I would like to run them at certain times in the db. How
can I add code to a click of a button event but not come
up with any message boxes or errors even if there is no
matching criteria.

Thanks

Terry
 
The easiest way way to be use a macro.
Set the first instruction to turn off warnings (SetWarnings I think).
The next 2 will run your 2 queries (OpenQuery I think).
Then turn your warnings back on.

Regards,
Andreas
 
I'd argue whether using a macro is "the easiest way", but even if it is,
using VBA is better because it gives you the opportunity to capture details
about any errors that might occur while running the queries.

CurrentDb.QueryDefs("MyAppendQuery").Execute, dbFailOnError

is how to run a stored query.
 
Forgot to mention that when you use the Execute method, you don't get the
"You're about to insert..." message.

And if you need to know how many records were affected by the query, you can
use:

Dim qdfCurr As DAO.QueryDef

Set qdfCurr = CurrentDb().QueryDefs("MyAppendQuery")
qdfCurr.Execute, dbFailOnError
MsgBox "Running MyAppendQuery affected " & _
qdfCurr.RecordsAffected & " records."
 
I was going to ignore this.
Honestly.
But then ...

As far as arguing about the "easiest way", I can't see the point,
especially since I suspect that my 2 finger typing skills would put me
at a severe disadvantage :-)

As far as VBA being the better way is concerned, absolutely no argument
whatsoever!
I had even thought about mentioning transactions to ensure that either
both queries work, or none.

And then I looked at the question, asked myself what level of knowledge
this represented and then suggested what I thought would be the easiest
way to get this level of automation implemented based on my conclusion
(and absolutely no disrespect towards Terry intended).

Either way, 2 solutions have been suggested and it is always good to
have choice :-)

Regards,
Andreas
 
Back
Top