We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Currentdb.Execute - Return Error if SQL does not process

Sean Strickland
on
Medium Priority
2,010 Views
Last Modified: 2013-11-27
I'm trying to run some INSERT and UPDATE queries using CurrentDb.Execute.  I've noticed a problem, however... if there is an error in the SQL and the SQL cannot be processed, there's no way that I've seen to trap the error.  If I use DoCmd.RunSQL instead, I can see the error message if SetWarnings is set to True; but not if it's set to False.  I don't want an access message popping up in my application regardless, though.

Is there any way to trap errors when CurrentDb.Execute does not execute my SQL properly?  I want to be able to notify my users that the information did not save, and I'm using a custom (unbound) form that runs multiple INSERT/UPDATE queries when the save button is clicked.
Comment
Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Public Function mFx()


On Error Goto mFxErrTrap
CurrentDb.Execute strSQL, dbFailOnError

'more code

mFxExit:
Err.Clear
Exit Function

mFxErrTrap:
   msgbox "Some Error"
   Goto mFxExit
Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
That is the basic drill ... you need to use the dbFailOnError  ... and put in your error trapping code - to whatever degree you need ... specific errors you expect, etc.


Public Function mFx()
Dim strSQL
strSQL = "Your sql statement ........"

On Error Goto mFxErrTrap
CurrentDb.Execute strSQL, dbFailOnError

'more code

mFxExit:
Err.Clear
Exit Function

mFxErrTrap:
   msgbox "Some Error " & Err.Number & "  " & Err.Description
   Goto mFxExit

End Function   ' forgot this

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Sean StricklandDirector, Analytics & Market Research

Author

Commented:
I forgot all about the dbFailOnError piece.  I've used it before, but it's been a while.  Let me make sure it works before I give you the points. :)

I'll try to test tomorrow when I'm back in the office.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
It works ... I *promise* ... I use it everywhere.  I rarely use SetWarnings, etc.

mx
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.