Automatically Approve (Yes) Delete & Append Queries

Hello Experts,

I have a macro that is running several delete and append queries, but this still requires the user to click through all the "Yes" approval dialog boxes.  Is there a way to get these to approve automatically without a user's intervention?

Thanks
ShadowITAsked:
Who is Participating?
 
Paul_Harris_FusionCommented:
If you execute your queries as follows:


    DBEngine(0)(0).Execute "Q1", dbFailOnError

you will not get the message
0
 
Rey Obrero (Capricorn1)Commented:
Office button > options > client settings
Confirm section
uncheck Action queries
            record changes
0
 
ShadowITAuthor Commented:
Capricorn1...I don't see client settings within Access Options.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I suggest you use the example posted by Paul as it is *much* safer than turning off warnings globally.

DBEngine(0)(0).Execute "Q1", dbFailOnError

Or

With CurrentDb
   .Execute "query1", dbFailOnError
   .Execute "query2", dbFailOnError
   .Execute "query3", dbFailOnError
 ' and so on
End With

and with the dbFailOnError option, errors that *you want to know about* will be raised, which you can trap with Error handling.

mx
0
 
Rey Obrero (Capricorn1)Commented:
in access 2007

Office button > options > Advanced
Confirm section
uncheck Action queries
            record changes
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
t is possible to globally turn off the security warnings but I would not do it. You will find it will get very easy to accidentally destroy your database. I have seen this happen to many people that go this route.

If you do decide to turn off the security warnings globally make sure that your database is spit and that you make lots and lots of backups.

0
 
Rey Obrero (Capricorn1)Commented:
that will only happen when the app is not designed properly...
0
 
kmslogicCommented:
In your macro you need to add a SetWarnings action -- set it to false to turn them off, then back to true when you are done.  SetWarnings is hidden in recent versions of Access you must click "show all actions" on the ribbon when you are creating the macro to see it.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
NOTE:  Be EXTREMELY careful with SetWarnings = False.  If you forget to execute a SetWarnings True ... and/or you code errors out ... leaving a False condition in effect ... you will run into a load of other undesirable issues ... such as ...

You open a form in Design view .... you make some changes ... you decide NOT to keep those changes ... you click the Close button ... expecting to see a WARNING message asking Do you want to ....
You WILL NOT see that message and the changes will be saved ... if a prior SetWarnings False is still in effect.  There are many other situations.

mx
0
 
ShadowITAuthor Commented:
All of these worked perfectly...thanks all!
0
 
kmslogicCommented:
DatabaseMX I tested that theory but it still does ask you if you want to save changes to a form even if SetWarnings is false  I believe SetWarnings only applies to queries.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I tested it and it does not for me ...
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
And I just double checked again (A2003)

In the vba Immediate Window, enter

docmd.SetWarnings false

Then open a Form in design view, move some control around ... and hit the red X button.  There is no prompt to Save Yes/No.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Same in A2010.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Same thing with a query (or other object).  Open query in design view, add/delete/move something ... hit red X ... *no prompt*.  It automatically Saves ...

mx
0
 
kmslogicCommented:
test it in a macro window with just setwarnings false (in other words you forgot to put setwarnings true later on in the macro)
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well, why?
If you run code, and Set Warnings is not put back to True, you are hosed.

Did you try the test I showed.  It's been like this for years.  No theory, just fact.

mx
0
 
kmslogicCommented:
Yes I tried docmd.setwarnings in vba as you said and it works as you said, but just as "facty" is that I tested it in a macro before I posted and it doesn't stick after the macro completes.  Since the author of this question was using a macro to run their queries it takes a bit risk out I'd say.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Not in my mind. It's bad practice all the way around.  Bet hey ...

mx
0
 
kmslogicCommented:
Some people don't use VBA with Excel.  Using macros, what is your alternative?  Did you test the behavior I witnessed with a macro btw?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I believe you :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.