Results 1 to 13 of 13
  1. #1
    Gail is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    34

    Procedure doesn't work when executed, but works when debugging

    Hi,



    I have an Access Form that calls a Stored Procedure in SQL Server.
    The end result of the Stored Procedure is it adds detail records to the table that the Access Form is based on.
    in my test it should add 20 detail records.

    When I execute the Stored Procedure directly from SQL Server, it works correctly, adding 20 records.
    So I know the Stored Procedure works.

    When I call the Stored Procedure from Access, it only adds 9 records.

    But when I debug Access and step through the access code to call the Stored Procedure, it correctly adds 20 records.

    Does anyone have any ideas why Access wouldn't work when running the program, but works when debugging.

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Can you show the process code you are using to call the SP?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Gail is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    34
    Quote Originally Posted by Minty View Post
    Can you show the process code you are using to call the SP?
    20 tmpExecSPString = _
    "DECLARE @rvErrorSwitch int; " & _
    "EXEC dbo.uspNewSalesOrderTemplateItems " & _
    Forms![GlobalVar]![SPExecErrorVar] & ", " & _
    "'" & prmSOType & "', " & _
    prmTemplateID & ", " & _
    prmSalesOrderID & ", " & _
    prmCustomerID & ", " & _
    prmPriceLevel & ", " & _
    "@ErrorSwitch = @rvErrorSwitch OUTPUT; " & _
    "SELECT @rvErrorSwitch;"

    20 tmpReturnValue = 0
    30 tmpErrorSwitch = False


    40 Set cdb = CurrentDb
    50 Set qdf = cdb.QueryDefs("brUspMiscDataSelect") ' NOTE: this is an Access Query that has the connection string to SQL Server


    60 qdf.SQL = tmpExecSPString


    70 Set rs = qdf.OpenRecordset
    80 If rs.EOF Then
    90 tmpErrorSwitch = True
    100 Else
    110 If (rs(0)) < 0 Then
    120 tmpErrorSwitch = True
    130 Else
    140 tmpReturnValue = (rs(0))
    150 End If
    160 End If
    170 rs.Close


    180 Set rs = Nothing
    190 Set qdf = Nothing
    200 Set cdb = Nothing

  4. #4
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Did you know you could simply output the desired result directly from the SP without declaring and using the Output clause?
    You could also adjust the SP to return a meaningful result directly without you needing to open the recordset and process it.

    I only normally process one command through a pass-though query, and I use a generic routine to accept the T-SQL and a flag to indicate if it returns records or simply need to run an action process.

    If you set up a pass through query called qpt_Generic you can then do something like
    Code:
    Public Sub sSendToPT(strQuery As String, bRetRecs As Boolean)  
        
        Dim db As DAO.Database
        Dim qDEF As QueryDef
        
        Set db = CurrentDb()
        
        Set qDEF = db.QueryDefs("qPT_Generic")
        qDEF.Connect = db.TableDefs("ReplacethisWithALinkedTablename").Connect
        qDEF.SQL = strQuery
        qDEF.ReturnsRecords = bRetRecs
        
        If Not bRetRecs Then        ' We Want to execute an INSERT, UPDATE etc. type process
            db.Execute "qPT_Generic", dbSeeChanges
        Else
            qDEF.Close
        End If
        
        Set qDEF = Nothing
        Set db = Nothing
    Then to use it (assuming your SP returns the ErrorSwitch as it's output)


    Code:
    strSQL = " EXEC dbo.uspNewSalesOrderTemplateItems " & Forms![GlobalVar]![SPExecErrorVar] & ", '" & prmSOType & "', " & prmTemplateID & ", " prmSalesOrderID & ", " &prmCustomerID & ", " & prmPriceLevel 
    
    Debug.print strSQL
    
    sSendToPT strSQL, True
    
    tmpReturnValue  = DLookup("ErrorSwitch", "qpt_Generic")


    Last edited by Minty; 03-07-2023 at 11:11 AM.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Gail is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    34
    thank you so much, I will definitely try this way and let you know how it goes.

  6. #6
    Gail is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    34
    I've tried using your code and I keep getting the message "Can't execute a Select query"
    I have returnrecords = true, so that I can check the errorswitch.

  7. #7
    Gail is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    34
    I am still struggling with this problem.

    *************************
    Example 1:

    tmpExecSPString = _
    "DECLARE @rvErrorSwitch int; " & _
    "EXEC dbo.uspNewSalesOrderTemplateItems " & _
    Forms![GlobalVar]![SPExecErrorVar] & ", " & _
    "'" & prmSOType & "', " & _
    prmTemplateID & ", " & _
    prmSalesOrderID & ", " & _
    prmCustomerID & ", " & _
    prmPriceLevel & ", " & _
    "@ErrorSwitch = @rvErrorSwitch OUTPUT; " & _
    "SELECT @rvErrorSwitch;"

    Set cdb = CurrentDb
    Set qdf = cdb.QueryDefs("brUspMisc") ' NOTE: this is an Access Query that has the connection string to SQL Server
    qdf.SQL = tmpExecSPString
    qdf.ReturnsRecords = true

    ‘ Check the ErrorSwitch by either opening a Recordset or using DLookup on the Query

    *************************
    Example 2:
    tmpExecSPString = _
    "EXEC dbo.uspNewSalesOrderTemplateItems " & _
    Forms![GlobalVar]![SPExecErrorVar] & ", " & _
    "'" & prmSOType & "', " & _
    prmTemplateID & ", " & _
    prmSalesOrderID & ", " & _
    prmCustomerID & ", " & _
    prmPriceLevel & ";"

    Set cdb = CurrentDb
    Set qdf = cdb.QueryDefs("brUspMisc")
    qdf.SQL = tmpExecSPString
    qdf.ReturnsRecords = false
    cdb.Execute "brUspMisc"

    ‘ No ErrorSwitch to check

    *************************

    In Example 1, the Stored Procedure doesn’t complete processing. It only adds 9 records when it should have added 20.
    (It worked during Access debugging because it had time to complete when stepping through the procedure)
    But I am able to pass back an ErrorSwitch to Access so I can check if there were any execution errors on the SP.
    If I set ReturnsRecords to true, I get the error “"Can't execute a Select query"”

    In Example 2, the SP works correctly and adds 20 records.
    But I am not able to pass back an ErrorSwitch to Access, either by defining an ErrorSwtich or using the Return statement on the SP.
    If I set ReturnRecords to false, then I can’t check the ErrorSwitch either through a Recordset or DLookUp

    I am obviously missing something or don’t understand.
    In an Access Procedure, is there a way to check for Errors when executing an update Stored Procedure?

  8. #8
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Put the error flag into the SP.
    At the end of the SP put SELECT @MyErrorFlag as ErrReturn as the final statement, and then you can use returns records.

    Just ensure there is always a flag set error or not.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    Gail is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    34
    thank you for replying.

    I am probably not explaining things clearly.
    But I have done that. (Please see Example 1)
    The problem is, when I set Return Records to true, the SP doesn't complete processing. it only adds 9 records, not 20.

  10. #10
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Sorry - I hadn't realised that was the case.
    There must be something else causing the behaviour, as with a pass through query all the processing is handled by the server, and it either returns records or doesn't.

    If it returns records (e.g. Your error flag) then access should wait until the SP Executes (and it could take a number of seconds, but that won't matter).
    Could you post up the SP code, in case there is a red flag we're missing? Please use the code tags to preserve any formatting (Use the # in the editor)

    Are any of the underlying records held open somewhere else (a sub form or something else?) preventing an update?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  11. #11
    Gail is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    34
    Thank you again for your reply, I appreciate your help.
    This Stored Procedure creates Sales Order Detail records from a Template.
    It is fairly long, so I tried to simplify it by putting in comments instead of some of the statements.

    This SP works correctly when executed from SQL Server.
    It also works correctly when executed from Access VBA when ReturnRecords is set to false.
    It doesn't work correctly when executed from Access VBA when ReturnRecords is set to true.

    USE [My Database]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO


    ALTER PROCEDURE [dbo].[uspNewSalesOrderTemplateItems]


    -- Input Variables are defined
    @TemplateErrorSwitch Int OUTPUT


    WITH RECOMPILE
    AS


    SET NOCOUNT ON;
    -- I have tried it both ways with the SET NOCOUNT statement and without.


    -- Temporary Variables are defined
    DECLARE @SaveErrorNo Int;
    DECLARE @tmpTemplateDetailID int;


    SET @TemplateErrorSwitch = 0;
    SET @SaveErrorNo = 0;


    DECLARE TDCursor CURSOR READ_ONLY
    FOR
    SELECT TemplateDetailID,
    -- Other fields from the TemplateDetail table
    FROM TemplateDetail
    WHERE Quantity <> 0;
    --The user will enter a quantity in the Template Detail records for the ones they want copied to the Sales Order.

    OPEN TDCursor;


    BEGIN TRY


    FETCH NEXT FROM TDCursor INTO
    @tmpTemplateDetailID,
    -- Other fields from the TemplateDetail table;


    WHILE @@FETCH_STATUS = 0
    BEGIN
    IF @tmpItemID > 0
    -- there is an Item ID, then it checks other data and looks up the pricing for that Item for that Customer, etc.
    -- Creates the Sales Order Detail Record
    ELSE
    -- there isn’t an Item ID, then it creates the Sales Order Detail Record from the information from the Template.


    FETCH NEXT FROM TDCursor INTO
    @tmpTemplateDetailID,
    -- Other fields from the TemplateDetail table;
    END


    END TRY


    BEGIN CATCH
    SET @SaveErrorNo = error_number();
    END CATCH


    IF @SaveErrorNo <> 0
    SET @TemplateErrorSwitch = 1;


    CLOSE TDCursor;


    DEALLOCATE TDCursor;

  12. #12
    Gail is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    34
    I re-wrote the Stored Procedure so that it doesn't use the cursor.
    It works now.

  13. #13
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Sorry I missed you posting the SP!

    I dislike cursors a lot and can honestly say I haven't used them for about 8 years, in most cases they can be written out in a more elegant fashion, so I'm glad you have a solution, and thank you for posting it back for other users.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 09-21-2020, 12:51 PM
  2. Replies: 3
    Last Post: 10-09-2019, 10:26 AM
  3. Replies: 5
    Last Post: 01-31-2018, 01:44 PM
  4. Replies: 8
    Last Post: 07-03-2015, 05:03 PM
  5. Replies: 8
    Last Post: 04-23-2015, 06:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums