Solved

Access 2007 Macro hangs with SetWarnings

Posted on 2011-03-06
6
990 Views
Last Modified: 2012-06-21
Created a Macro in Access 2007 to run a query.  The query runs fine, database is trusted, the confrim action queries is off.  When I setwarnings to No or Yes before the query, the macro hangs.  When I remove the setwarnings, everything runs fine again, but I don't want the confirmation prompts.  I would like to run this macro as a scheduled task so I do not want any prompts.

Suggestions?
0
Comment
Question by:mjwcai
  • 3
  • 2
6 Comments
 

Author Comment

by:mjwcai
ID: 35047570
I should also add that when I single step through the Macro and use Setwarning, it will go to the next step in the macro, but then hangs on the query step.  Removing the setwarnings and then using single steps..., the macro completes fine.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 250 total points
ID: 35047663
Consider using VBA code to run this action query like so:

Public Function mRunMyQuery

    CurrentDB.Execute "YourActionQueryName", dbFailOnError

End Function

This avoids the prompts - and will generate a trappable error if the action query should fail for some reason.  Using SetWarnings False is very dangerous in general.

mx

 
0
 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 250 total points
ID: 35047670
Assuming you are are not using a Web database:

If it were me I would  use VBA so that you can have error handling id needed.

I would use this VBA code command: CurrentDB.Execute  
Using this command you do not need to set the warning to false.  I use it in scheduled databases with great success.

It is possible to put this VBA code in a function and call it from your Macro.



0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:mjwcai
ID: 35047782
How do I get a VBA to work in a scheduled task?
0
 
LVL 21
ID: 35048039
As a scheduled task I would assume you would be using the Windows Task Scheduler.

See:
How to Start Access by Using the Windows Schedule Service

Alternative:

Scheduler Utility




0
 

Author Comment

by:mjwcai
ID: 35048292
I guess my question was how do I get the VBA into the macro so I can run as a scheduled task - I have never used VBA before - but I figured it out and got it working
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question