none
How to make sure a passthrough query has completed successfully before executing additional code RRS feed

  • Question

  • My code executes a passthrough query that can take some time to complete. I want to make sure the SQL stored procedure has completed and returned all records to the query before I execute additional commands that act on those records. Should I use DoEvents or DBEngine.Idle (with additional arguments), or something entirely different?

    I would also like to test that the SQL stored procedure executed without error. How can I additionally capture the return value of the SP?

    Tuesday, August 11, 2020 11:12 AM

Answers

  • I think you are running a PT query that returns records.

    If so, doing rs.MoveLast should be all that is needed.


    -Tom. Microsoft Access MVP

    • Marked as answer by MDS_LV Friday, August 14, 2020 5:00 PM
    Thursday, August 13, 2020 3:46 PM

All replies

  • Are you using ADO?  DAO?  Can you post the bit of code that you're using to run your stored procedure?

    -Bruce

    Thursday, August 13, 2020 2:30 PM
  • I think you are running a PT query that returns records.

    If so, doing rs.MoveLast should be all that is needed.


    -Tom. Microsoft Access MVP

    • Marked as answer by MDS_LV Friday, August 14, 2020 5:00 PM
    Thursday, August 13, 2020 3:46 PM
  • Have you checked if it throws a Timeout error ?

    Maybe ...thinking...if the stored procedure takes too much time on SQL maybe you could have it running...check if its running : https://stackoverflow.com/questions/25846306/check-if-stored-procedure-is-running and maybe have it populate a temp table on sql...if the table has records you would then pull them via PT...you could also enclose the whole output in a transaction and so if something goes wrong the temp table will surely will be empty

    Friday, August 14, 2020 7:47 AM
  • Yes, I am running a stored procedure that returns records. Your suggestion works great. Thanks.
    Friday, August 14, 2020 4:59 PM
  • Thanks for link to stackoverflow post. Lots of good ideas there. I'll need to study and try them.
    Friday, August 14, 2020 5:09 PM