?
Solved

Currentdb.Execute - Return Error if SQL does not process

Posted on 2009-02-11
4
Medium Priority
?
1,849 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.
0
Comment
Question by:SeanStrickland
  • 3
4 Comments
 
LVL 75
ID: 23618021
Public Function mFx()


On Error Goto mFxErrTrap
CurrentDb.Execute strSQL, dbFailOnError

'more code

mFxExit:
Err.Clear
Exit Function

mFxErrTrap:
   msgbox "Some Error"
   Goto mFxExit
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 23618038
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
0
 
LVL 11

Author Comment

by:SeanStrickland
ID: 23618164
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.
0
 
LVL 75
ID: 23618175
It works ... I *promise* ... I use it everywhere.  I rarely use SetWarnings, etc.

mx
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month14 days, 17 hours left to enroll

839 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