September 8, 2009 at 2:24 am
Hi,
I work with Access (2003 & 2007) as front end to a sql server 2008
In my stored procedures i always use 'Begin Try - End Try - Begin Catch - End Catch' structure
In case of a error Access is only showing the ODBC error and not the error that SQL Server returns from the Catch statement (see below)
How can i return the SQL Server error to Access?
Thanks
Ivo
BEGIN CATCH
SELECT ERROR_NUMBER() ErrorNBR, ERROR_SEVERITY() Severity,
ERROR_LINE () ErrorLine, ERROR_MESSAGE() Msg
PRINT 'Er is een fout opgetreden'
END CATCH
October 10, 2009 at 9:14 am
What kind of statements are inside your begin try - end try block, do you know what causes the error, and how are you executing the sp from Access?
If you are using pass-thru query to execute the sp:
insert, update, and delete queries - the pass-thru has a setting "ReturnsRecords" but you would need to set that = Yes and then provide
SELECT
NULL ErrorNBR,
NULL Severity,
NULL ErrorLine,
NULL Msg
at the end of the TRY block so that the query returns 1 record even when it succeeds.
select queries - if there is an error then the sp returns 2 recordsets - one with no records & the other with your error - but Access can only see the first one. Not sure how to deal with that one.
However, none of the above would return a visible ODBC error 3146 message. If I am not understanding your question just let me know.
October 12, 2009 at 5:09 am
You need to iterate through the Errors collection of the connection you are using - this code from MSDN loops through the error collection of a connection called cn and applies the errors to a listbox on Form2 - you should be able to get the basic idea from this
Private Sub ErrorLog()
. . .
Dim errLoop As ADODB.Error
. . .
' Loop through each Error object in Errors collection.
For Each errLoop In cn.Errors
Dim strError(5)
Dim i As Integer
strError(0) = "Error Number: " & errLoop.Number
strError(1) = " Description: " & errLoop.Description
strError(2) = " Source: " & errLoop.Source
strError(3) = " SQL State: " & errLoop.SQLState
strError(4) = " Native Error: " & errLoop.NativeError
' Loop through the five specified properties of Error object.
i = 0
Do While i < 5
Form2.lstErrors.AddItem strError(i)
i = i + 1
Loop
Form2.lstErrors.AddItem ""
Next
' Create string for summary count of errors.
c = cn.Errors.Count & " provider error(s) occurred."
' Display a count of the provider errors.
Form2.lstErrors.AddItem c
Form2.lstErrors.AddItem ""
' Clear the Errors collection.
cn.Errors.Clear
End Sub
October 13, 2009 at 3:15 am
Hi Chris, why are you using
Form2.lstErrors.AddItem ""
twice?
October 13, 2009 at 3:18 am
I'm not - as I said - this is code from Microsoft which I posted as an example
From the look of it, this is just to insert a blank line in the listbox - it shows the errors then a summary count with a couple of blank lines
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply