Tracking SQL error in VBA

[RichO]

Greetings!

I have a piece of code that uses SQL for an insert query. Every once in a great while the record that it is supposed to insert does not get inserted and I'm guessing that there is some kind of key or null violation that it encounters.

No error is displayed when this happens and there is currently no error trapping code.

Will a simple On Error GoTo and a MsgBox Err.Description tell me when the SQL fails or is there some other kind of error handling that needs to be done to inform me of a SQL error?

Thanks!

 

[Isskint]

Do you want to do this at user level or for debug?

If you could 'map' the error code that come up under all situations you could include an error handler Select Case to inform the user why there is an error (rather than the vague MS message).

At debug level just put a msgbox SQLstr before the RunSQL command, and you will be able to check it through.

 

[jdraw]

Get yourself a copy of the free utility MZTools for VBA
http://www.mztools.com

It has many features that you will come to enjoy. The first is the add Error handler at the click of the mouse.

As for Debug info see
http://www.cpearson.com/excel/debug.htm

 

[RichO]

It's just for the one time purpose of finding out why sometimes the record doesn't insert. Once I find that out, I can fix whatever is wrong so it doesn't happen anymore.

I am not the one using the DB so I need the user to inform me when it happens and displays the error.

Thanks

 

[jdraw]

Who is responsible for the code?
Put in an error handler and have it record something specific (even to a table if necessary).
You check the error handler output (or table) and then resolve the problem.

Do you really think the user who gets the error will know exactly what happened?

I'm not sure if you are the Access/vba programmer or not, but I am a little surprised (based on your 900+ posts) that you don't use MZTools, or include an Error handler in programs. Again I don't know who coded what, but you did indicate you would fix it once you find out what the issue is.

 

[Isskint]

It's just for the one time purpose of finding out why sometimes the record doesn't insert. Once I find that out, I can fix whatever is wrong so it doesn't happen anymore.

I am not the one using the DB so I need the user to inform me when it happens and displays the error.

Thanks

In the error handler save the SQLstring (to table ot text file) and instruct the user to email that to you or read it to you on the phone.

 

[RichO]

The user would call me on the phone as soon as he encounters the error. I just need to know what I'm looking for.

Saving the SQL and error code to a table is a good idea. I just wanted to make sure the standard error handler (on error goto) would catch a SQL error such as a key or null violation so I can record it.

Thanks!

 

[Reflex_ht]

Why don´t you send the error info (error nr, form, user, date, time ...) automaticely to you over email?

I use for my error handler this:

DoCmd.SendObject acSendNoObject, , , "my@email.com", , , "Error Message", "Error!" & vbNewLine & "ErrorNr: " & ErrNr & vbNewLine & "ErrorDescription: " & ErrB & vbNewLine & "On Form: " & ErrF & vbNewLine & "Function: " & ErrC & vbNewLine & "Error Time: " & ErrD & vbNewLine & "Error by User: " & ErrSB, False

It sends the email in the bacground and the User hase nothing to do with that. I don´t know how your users are but my users dont think that errors are a big thing and so they often don´t tell me when someone hapens because of that i had to insert that in the error handler.

There is also a code for puting all that Error Info in a Table.

 

[PeterF]

No error is displayed when this happens and there is currently no error trapping code.

Do you ask for a error when running your SQL?

CurrentDb.Execute (strSql)[B], dbFailOnError[/B]

If you don't there wil be no warning, errorhandler or not.

 

[RichO]

Well, there is only one user of this DB and he is very responsible about letting me know errors because he wants them fixed ASAP.

E-mailing the error data is also a good idea.

Good to know about dbFailOnError. I would not have known to put that in there. So that command allows the error handler to pick up the error?


Thanks for all the tips!