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

ions

Access User
Local time
Yesterday, 19:18
Joined
May 23, 2004
Messages
792
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

1715901081603.png


Code:
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

Lifelong Learner
Local time
Yesterday, 19:18
Joined
Mar 14, 2017
Messages
8,988

Isaac

Lifelong Learner
Local time
Yesterday, 19:18
Joined
Mar 14, 2017
Messages
8,988
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

Code:
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

Active member
Local time
Yesterday, 19:18
Joined
Jan 22, 2016
Messages
291
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

Super Moderator
Staff member
Local time
Today, 12:18
Joined
Jan 20, 2009
Messages
12,866
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

Access User
Local time
Yesterday, 19:18
Joined
May 23, 2004
Messages
792
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

Lifelong Learner
Local time
Yesterday, 19:18
Joined
Mar 14, 2017
Messages
8,988
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
 
Last edited:

ions

Access User
Local time
Yesterday, 19:18
Joined
May 23, 2004
Messages
792
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.
 

Users who are viewing this thread

Top Bottom