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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul_Harris_FusionCommented:
If you execute your queries as follows:


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

you will not get the message
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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 MVPDesigner and DeveloperCommented:
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 Architect / Systems AnalystCommented:
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 Architect / Systems AnalystCommented:
I tested it and it does not for me ...
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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 Architect / Systems AnalystCommented:
Same in A2010.

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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 Architect / Systems AnalystCommented:
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 Architect / Systems AnalystCommented:
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 Architect / Systems AnalystCommented:
I believe you :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.