ms-access - how to automatically select yes in warning message boxes

Question

in ms-access i am running a macro that runs several queries, during the execution of a query a message box appears

"you are about to run an update.......... are you sure you want to run this query ? "

how can i automatically select for all such cases so that macro runs without human intervention.

Answer 1

You can turn off temporary the warnings like this:

DoCmd.SetWarnings = False
DoCmd.RunSQL ...
DoCmd.SetWarnings = True

Answer 2

It is generally best to use Execute in such cases in order to trap errors:

Dim db As Database, qdf As QueryDef, strSQL As String

Set db = CurrentDb
Set qdf = db.QueryDefs("Query17")
qdf.Execute dbFailOnError
Debug.Print qdf.RecordsAffected

Or

strSQL="UPDATE SomeTable SET SomeField=10"
db.Execute strSQL, dbFailOnError
Debug.Print db.RecordsAffected

Trapping errors with dbFailOnError and an error trap is more or less essential and there are a number of other useful aspects to the Execute Statement

Answer 3

To avoid having to write the code @Remou supplies every time you execute arbitrary SQL you could use my SQLRun function, which is designed as a dropin replacement for DoCmd.RunSQL and avoids all the problems therewith.