Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1037
  • Last Modified:

Access 2007 Macro hangs with SetWarnings

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
mjwcai
Asked:
mjwcai
  • 3
  • 2
2 Solutions
 
mjwcaiAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
mjwcaiAuthor Commented:
How do I get a VBA to work in a scheduled task?
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
 
mjwcaiAuthor Commented:
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now