How do you know if an Error occured in your Stored Procedure from MS Access Pass-through Query?

[ions]

Hello MS Access Expert,

Suppose I have the below stored procedure. Note, that currently it will throw a division by 0 error. In MS Access when I run the pass-through query, I receive an empty recordset which could imply no results when in fact there was a Division by 0 error.

How do I communicate the Division by 0 Error to the MS Access user?

Thank you

Create Or Alter proc mySp
 AS
 Begin
    Declare @a int, @b int

    Begin try

        Set @a = 1
        Set @b = 0

        Select *, @a/@b as Result from project

    End Try
    Begin Catch
        Select ERROR_MESSAGE() as result;
        throw
    End Catch
End

 

[Isaac]

Capture SQL Server error (RaiseError) via MS Access, after FORM-based update error

I have a SQL Server table with a trigger after insert, update, and delete. If a certain condition is found, the trigger rolls back the transaction and raises a custom error using raise error. I've verified it works properly. The basic idea is that during such times as I need to "freeze" all...

www.accessforums.net

 

[Isaac]

Hello MS Access Expert,

Suppose I have the below stored procedure. Note, that currently it will throw a division by 0 error. In MS Access when I run the pass-through query, I receive an empty recordset which could imply no results when in fact there was a Division by 0 error.

How do I communicate the Division by 0 Error to the MS Access user?

Thank you

View attachment 114166

Create Or Alter proc mySp
AS
Begin
    Declare @a int, @b int

    Begin try

        Set @a = 1
        Set @b = 0

        Select *, @a/@b as Result from project

    End Try
    Begin Catch
        Select ERROR_MESSAGE() as result;
        throw
    End Catch
End

I faced this and it was one of my disappointments in combining Microsoft access with SQL server. But I think in the end if you raise the right kind of error what the right level of severity it might, might in some cases come back to the calling application but likely not to Microsoft Access unfortunately

 

[tvanstiphout]

I used a sproc without any error handling, just simply:
select * from sometable where PK = 1 / 0;

When executing that with a PT query, the error is caught in VBA, and the Errors collection is populated:
ErrorNo=8134 [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Divide by zero error encountered. ODBC.Database
ErrorNo=3146 ODBC--call failed. DAO.Database

 

[Galaxiom]

Instead of a PT, use an ADODB command to run the SP and Set the Form Recordset property to the returned recordset if all is well.
Use TRY...CATCH in the SP and have it write any error to an output recordset.

Multiple recordsets can returned from a SP to VBA via ADODB. One could be used for the error. Check in VBA if it has any records and display them instead if they exist. Non fatal errors from the SP could also be returned with the data.

 

[ions]

Thank you for everyone's response.

tvanstiphout you are correct without a Try Catch MS Access displays the error. Not including the Try-Catch may be sufficient for simple SP.

Galaxiom I don't have much experience with ADODB especially with SP returning multiple recordsets. Do you have any code handy that you could share?

​

 

[Isaac]

Multiple recordsets can returned from a SP to VBA via ADODB

Good luck with that ... I've tried that extensively, it's very buggy and often the second one just doesn't work
Your best bet is the errors collection

 

[ions]

I was doing some testing today in regards to Errors showing up in Pass-through Queries with Stored Procedure having a Try Catch.

Raising custom Business Logic Errors works well using RAISERROR ('My message', 11, 1). Also, I was getting the error 'Invalid column name' after changing a field name in SSMS but not updating the stored procedure.

It's just the Select 1/0 Division error that returns an empty recordset if I use a Try Catch. Interestingly, as mentioned above, without a Try Catch the division error does show up in MS Access.