Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access 2007 Macro hangs with SetWarnings

Posted on 2011-03-06
6
Medium Priority
?
1,030 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 - Microsoft MVP, Access and Data Platform) earned 1000 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 1000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

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.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

715 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