Trapping SQL Server errors in Access

  • 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

  • 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.

  • 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

  • Hi Chris, why are you using

    Form2.lstErrors.AddItem ""

    twice?

  • 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