0

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.

silverkid
  • 9,291
  • 22
  • 66
  • 92

3 Answers3

2

You can turn off temporary the warnings like this:

DoCmd.SetWarnings = False
DoCmd.RunSQL ...
DoCmd.SetWarnings = True
Nick Dandoulakis
  • 42,588
  • 16
  • 104
  • 136
  • This is bad advice as 1) you also need to add docmd.setwarnings = true to error handling; 2) running execute will give you much better errors. – Tony Toews Dec 09 '09 at 17:50
1

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

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
1

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.

Community
  • 1
  • 1
David-W-Fenton
  • 22,871
  • 4
  • 45
  • 58