How to handle and/or get rid of ODBC Call Failed?

I have Access 2003 linked to SQL Server 2005, - used to have Access backend and now must move to SQL Server . I am testing what happens if errors occur - When testing by deliberately creating a duplicate record in my form, this error came up ODBC Call Failed (# 3146)- and, it also includes a couple of anticipated SQL Server errors as well (ie Violation of Primary Key Constraint.. Cannot insert Duplicate Key in Object...). Q. is: how do I capture a duplicate error so I can create a generic duplicate msg for the user? Is it normal to have the 3146 error tagging along?

I have gotten this same error along with other DIFFERENT SQL errors, so the code that handles this error ( # 3146) cannot do anything other than show the message.

My thinking was that this error (#3146) was the first error, with the SQL errors included, the 2nd, 3rd. If this is true, then I would need to find the cause of the first error. Is this thinking correct? If NOT, how do I go about capturing the exact error for dups from SQL Server? If SO, I tried doing an Access trace & the closest thing I could find that seemed to be related to the ODBC call failed was an EXIT SQLErrorW stmt in the trace with return code 100 (SQL_NO_DATA_FOUND). The other 2 trace stmts for the other 2 SQL errors returned code 0 (SQL_Success). I am scratching my head here...

The best way to capture SQL errors and avoid the whole Access dialog boxes, is to use the Form_Error routine if you are using a form. something like the below can be customized to fit most errors generated by the ODBC:


Private Sub Form_Error(DataErr As Integer, Response As Integer)

Select Case DataErr
Case 3146 'Required Field ODBC Error 'Case 3146 To 3621 'ODBC Error
MsgBox "You must have data in all Required Fields before moving off the record or closing the form.", vbCritical, "Required Field Error #" + Str(DataErr) + " - All Required Fields Must Contain Data!"
Response = acDataErrContinue
Case 3146 To 3621
MsgBox "Unknown ODBC error occurred", vbCritical, "ODBC Error #" & Str(DataErr)
Response = acDataErrContinue
Case Else
MsgBox AccessError(DataErr)
Response = acDataErrContinue
End Select
End Sub

Thanks Vonnie - I do have error capturing routine in place under the form.error event. I tried editing it with the info. you provided. However, I am not sure it would ever get past 1st case on error# 3146? and in this case, with duplication being the cause, the error msg wasn't appropriate for the user.

Also, is that error "normal" or does it indicate an ODBC issue?

here is my error routine edited with your info:

Const conDuplicateKey = 3022
Const conODBCcallFailed = 3146
Const conNullNotAllowed = 3162
Dim strMsg As String
Dim strControl As String
strControl = Screen.ActiveControl.Name
'This is to handle error that occurs when user attempts to remove data from a field
'that is required (can not handle via field_before update event if against SQL Server)
'else will generate the following: You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162)
strMsg = ""
Select Case DataErr
Case Is = conNullNotAllowed
strMsg = "You must enter a value for this field. It cannot be left blank."
Case Is = conDuplicateKey
strMsg = "The values you entered would create either a duplicate field value or a duplicate record, which is not allowed."
Case Is = conODBCcallFailed
strMsg = "You must have data in all Required Fields before moving off the record or closing the form."
'strMsg = ""
Case 3146 To 3621
strMsg = strMsg & vbCrLf & "Unknown ODBC error occurred"
Case Else
strMsg = ""
End Select
If strMsg <> "" Then
If MsgBox(strMsg & vbCrLf & "Press OK to continue or Cancel to abort.", 1, Me.Name & " " & Str(DataErr)) = 2 Then
Me.Undo
Else
' Me(strControl).Undo ' wont work under all circumstances
End If
Response = acDataErrContinue
Else
Response = acDataErrDisplay 'show original message
End If

I really hate that 3146 error. It means SQL doesn't like something in your recordsource string, or you don't have References set correctly, or some other weird problem. I have spent alot of time trying to track down that error. If it is being generated by just one control, then you can put some tracking coding on that control and make sure you don't have some new strange type error. Otherwise try and update query using the suspect data, sometimes a query will give you a more helpful error. Good luck on the hunt.

I see that you're trapping ODBC error displayed by Access, but I don't see you retrieving the error message from the server, which usually are more informative.

I don't know anything about MS SQL, but with MySQL, whenever a Form_Error event is fired, I go to a general function to check if it's one of ODBC error and if so, call a pass-through query with the SQL statement "SHOW ERRORS;" which MySQL will then return its own message and number, then I refer to another function to parse which MySQL error code was triggered, and replace with a more user-friendly message or in some case, just display the error if it's clear enough on its own.

I don't know whether MS SQL uses "SHOW ERRORS;" as I'm sure that is MySQL's dialect, but am positive MS SQL does have its own procedure for retrieving errors which you can call through pass-through query.

HTH.

Well - besides the Q. of wether the ODBC- call failed is itself an error, I guess I was assuming that Access would pass the SQL error through - I do see the SQL errors along with this ODBC error. So still don't know if I am thinking about this correctly when I view this as getting 1 ODBC error and 2 SQL Server errors. I can't seem to access the SQL Errors using the Errors collection either, which I had tried previously.

Other than that I had come across some doc. about using SQLGetDiagRec - but that would involve going through a process of copying C+ code - and I am not even sure if I could use it within Access anyway...

As far as your comment: "am positive MS SQL does have its own procedure for retrieving errors which you can call through pass-through query" -- I don't know of any. I was just looking some more based on your comment & found the following, which looks rather painful:

****************************************************
I needed a similar thing once - return meaningful SQL error message to
Access client.

My kludgey fix was this

* get the list of SQL Server errors and put it in a local Access table.
* use pass-thru queries - execute them from a error-trapped subroutine in
Access
* wrap the base SQL statement with TransactSQL code to trap errors and write
them to an error log table
* link to the error log table (WORK_ERRORS)
* when an error occurs, poll WORK_ERRORS for the Username and query the
error number against the local list of SQL error messages.

It works pretty good, actually, but it's a total kludge involving a SQL
error log table, a SQL rows affected table, and a separate local Access
table.

Public Sub alterODBCrecords(cSQL As String)

'THIS ROUTINE UPDATES THE PASS-THRU QUERY WITH THE SQL STRING PASSED INTO
THE SUB
'THEN IT SENDS THE PASS-THRU QUERY TO THE SERVER FOR EXECUTION
'ALSO TRACK THE NUMBER OF ROWS UPDATED, AND THE ERROR CODE IF ANY

On Error GoTo errAlterODBC

Set qItem = db.QueryDefs("Q_PASS_THRU")
qItem.ReturnsRecords = False


'TRAP ERRORS ON SQL SERVER - WRITE TO LOG TABLE. HANDLES ROWS AFFECTED
BETTER
qItem.SQL = "DECLARE @ERR int SET @ERR = 0 "
qItem.SQL = qItem.SQL & "DECLARE @ROWSAFFECTED int SET @ROWSAFFECTED =
0 "
qItem.SQL = qItem.SQL & cSQL 'THIS cSQL IS THE BASE SQL STATEMENT YOU
WANT TO EXECUTE
qItem.SQL = qItem.SQL & " SELECT @ERR = @@ERROR, @ROWSAFFECTED =
@@ROWCOUNT "
qItem.SQL = qItem.SQL & " UPDATE WORK_ROWS_AFFECTED SET RowsAffected =
@ROWSAFFECTED WHERE AppUserID = '" & getLoggedUser() & "'; "
qItem.SQL = qItem.SQL & " IF @ERR <> 0 BEGIN INSERT INTO WORK_ERRORS
(AppUserID, errorNum) VALUES ('" & getLoggedUser() & "', @ERR); END "

qItem.Close
qItem.Execute


exitAlterODBC:
Exit Sub

errAlterODBC:
globalErr = "pass-thru error"


'RETRIEVE SQL SERVER ERROR - LOGGED IN SQL TABLE VIA ABOVE
Dim odbcErr As Integer, Set rs2 = db.OpenRecordset("SELECT errorNum FROM
WORK_ERRORS WHERE AppUserID = '" & getLoggedUser() & "';")
odbcErr = rs2("errorNum")
rs2.Close

'REFER TO LOCAL TABLE TO GET ODBC ERROR DESCRIPTION
dim odbcErrDescription As Variant
odbcErrDescription = DLookup("ErrorDescription", "Sys_SQLErrorMessages",
"ErrorMsg = " & odbcErr & "")
if isnull(odbcErrDescription) then
odbcErrDescription = "Error " & odbcErr & ": error description not found
else
odbcErrDescription = DLookup("ErrorDescription", "Sys_SQLErrorMessages",
"ErrorMsg = " & odbcErr & "")
End If
MsgBox "SQL Server error " & odbcErr & ": " & odbcErrDescription
Resume exitAlterODBC


End Sub

Joanne,

You're correct that Errors collection is empty whenever form's Error event has been fired; that's by design and documented by MS. Now, it may say it was for Access 95 & earlier, but this is important part below:

NOTE: Although this behavior has changed in Microsoft Access 97, you can still not trap the second part of the error. An event does fire when there is an error; however, there is no retrievable information about the error message. The event that fires only helps you to hide existing errors from a user.

Click to expand...

Which is how I ended up with my solution of using MySQL's 'SHOW ERRORS;' in a pass through to return the errors.

I'm surprised that MS SQL doesn't have similar function built-in. How old was that solution you posted? Could it be for a earlier version which since has changed?

With regards to the Errors collection: current help states:
When a ADO or DAO error occurs, the Visual Basic Err object contains the error number for the first object in the Errors collection. To determine whether additional ADO or DAO errors have occurred, check the Errors collection. The values of the ADO Number or DAO Number properties and the ADO Description or DAO Description properties of the first Error object in the Errors collection should match the values of the Number and Description properties of the Visual Basic Err object.

Now, since this refers to ADO or DAO errors, I would have thought that this would also include error msgs returned from a linked SQL Server form recordset, but apparantly not.

With regards to the solution I found:
the solution was posted sometime during 2005, so not too old... I am not going to use it though.

Joanne,

This is also correct. See, you can trap whenever you do it in VBA. You just can't trap it in Access Form's Error event because this is handled by Access, not by VBA (and by extension the developer). This is despite the fact that Jet (Access) does uses DAO to communicate with backend. So what I quoted from MS applies: When you suppress the error message generated by Access's form's event, you cannot get the information because it's not available. But if you did exactly same thing in VBA (e.g. let's say the error was a result of a failed update), all you would need is to create a identical query that has identical data in it then pass it off, and when it errors, you can review the Errors collection.

I did thought of an idea- if there was a simple way to capture the SQL statement that Jet sends to MS SQL, this could be used in a function to get the errors by repeating the SQL statement in a error-trapped VBA routine and examining the Errors collection. The only problem, unfortunately, is that to get that SQL statement would necessitate examining the log file, and that opens a whole new can of worms.

A slightly more popular option is to use "half-bound" form; I've seen postings from other users who claim to use bound forms for browsing, and when they need to edit, click a button which makes the form unbound, and they perform the update/insert manually and thus can trap the error. But IMO, that's too much work, considering that MySQL happily gives me the errors with that pass-through query so there's no real need for me to alter form's bindings.

Well Banana - I guess the user is just going to have to see those messages - I've spent enough time on this - I see what you are saying, but I don't want to clutter up my program with too many fixes... Thanks for the commentary - its been fun...

Quite understandable.

Good luck!