Results 1 to 15 of 15

How Do I Make Access VBA Wait for the Stored Procedure to Finish Executing

  1. #1
    fmuzungu is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Location
    Lusaka Zamba
    Posts
    8

    How Do I Make Access VBA Wait for the Stored Procedure to Finish Executing

    I am running an application with primary data sitting on SQL 2008 database while using Access 2013 as a front-end through linked tables. I have faced a problem with some of my stored procedures which are taking time to run on SQL but expected to produce a report within a second. Below is a VBA code for calling a report. I'm not getting any results because the report is produced before SQL finishes running the stored procedure. Anyone who can help in showing me How to make Access VBA wait for the Stored Procedure to finish executing?



    Private Sub ProcessReport_Click()
    Dim conn As ADODB.Connection
    Dim cmd As ADODB.Command

    Set conn = New ADODB.Connection
    conn.ConnectionString = "Driver={SQL Server};Server=freeday-HP;Database=TestDB;User Id=gigida;Password=dtf666;"
    conn.Open

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "rpt_TransactionsAuditReport"

    cmd.Parameters.Append cmd.CreateParameter("@BranchID", adVarChar, adParamInput, 6, Me.BranchID.Value)

    cmd.Execute
    conn.Close

    DoCmd.OpenReport "Transactions Audit Report", acViewPreview
    DoCmd.Close acForm, "Transactions Audit Report", acSaveYes
    End Sub

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,973
    Try a timer.
    Code:
    Dim Start As Double
    Start = Timer
    While Timer < Start + 3
         DoEvents
    Wend
    Also review https://stackoverflow.com/questions/...ore-continuing
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  3. #3
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    18,650
    You might also be able to create an output parameter in the stored procedure and test for it in your code. It shouldn't be able to continue until it's received that value.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  4. #4
    fmuzungu is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Location
    Lusaka Zamba
    Posts
    8
    Private Sub ProcessReport_Click()
    Dim conn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim Start As Double
    Start = Timer

    While Timer < Start + 20
    DoEvents
    Wend

    Set conn = New ADODB.Connection
    conn.ConnectionString = "Driver={SQL Server};Server=freeday-HP;Database=TestDB;User Id=gigida;Password=dtf666;"
    conn.Open

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "r_TrialBalance"

    ' cmd.Parameters.Append cmd.CreateParameter("@BranchID", adVarChar, adParamInput, 6, Me.BranchID.Value)

    cmd.Execute



    conn.Close

    DoCmd.Close acForm, "Trial Balance", acSaveYes
    DoCmd.OpenReport "Trial Balance", acViewPreview
    DoCmd.OpenForm "Trial Balance", acNormal
    End Sub

  5. #5
    fmuzungu is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Location
    Lusaka Zamba
    Posts
    8
    I've tried using a timer but still not getting any results.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,973
    Place timer code after the Execute comnand.

    Please use CODE tags for lengthy code to retain indentation and readability.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  7. #7
    fmuzungu is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Location
    Lusaka Zamba
    Posts
    8
    Private Sub ProcessReport_Click()
    Dim conn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim Start As Double

    Set conn = New ADODB.Connection
    conn.ConnectionString = "Driver={SQL Server};Server=freeday-HP;Database=TestDB;User Id=gigida;Password=dtf666;"
    conn.Open

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "r_TrialBalance"

    cmd.Execute

    Start = Timer

    While Timer < Start + 20
    DoEvents
    Wend

    conn.Close

    DoCmd.Close acForm, "Trial Balance", acSaveYes
    DoCmd.OpenReport "Trial Balance", acViewPreview
    End Sub

  8. #8
    fmuzungu is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Location
    Lusaka Zamba
    Posts
    8
    I'm so sure where I'm going wrong but the above code still returns no results

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,973
    If nothing in the referenced link works, try Paul's suggestion in post 3.

    Note that acSaveYes has nothing to do with saving data. It is to save design changes.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  10. #10
    Minty is online now Hacker
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    808
    As Paul stated if you force a return then Access will simply wait, (unless it actually times out).

    If the stored procedure is creating a table of results for your report to run against, why not simply return those results?
    Or store them in a table and run the report against that, checking that the table has valid data first.

    Are you sure the SP is running? I've had some quite complex SP's run to populate temp tables on form opening/load events and I can't say I have ever seen Access "beat" SQL server to not perform the stored processes first.
    Please use the star below the post to say thanks if we have helped !

  11. #11
    fmuzungu is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Location
    Lusaka Zamba
    Posts
    8
    Clearly as can be seen highlighted in blue below, the SP is running as is being commanded by the application via the VBA code. The SP is supposed to populate a table from where the command "DoCmd.OpenReport "Trial Balance", acViewPreview" is executed to get the report. Checking the table from where the report is drawn, it is not populated when executing the SP via the VBA code. The irony is the SP runs well and populates the report table when I execute the SP in SQL. Where could the problem be then?

  12. #12
    fmuzungu is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Location
    Lusaka Zamba
    Posts
    8
    Click image for larger version. 

Name:	SP.jpg 
Views:	15 
Size:	126.5 KB 
ID:	35444

  13. #13
    Minty is online now Hacker
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    808
    Try converting your SP call to a passthrough query (this is a bit of air code based on your previous posts);
    Code:
            Dim db As DAO.Database
            Dim rs As DAO.Recordset
            Dim qdfpt As QueryDef
    
    
            Set db = CurrentDb
            Set qdfpt = db.QueryDefs("passYourPassThroughStoredQuery")
            qdfpt.Connect = "ODBC;"Driver={SQL Server};Server=freeday-HP;Database=TestDB;User Id=gigida;Password=dtf666;"
            qdfpt.SQL = "exec dbo.r_TrialBalance @BranchID = " & Me.BranchID
    
            db.Close
    
    
            DoCmd.OpenReport "Trial Balance", acViewPreview
            DoCmd.OpenForm "Trial Balance", acNormal
    I believe that access will wait for the query to run - if you set your SP to return a result (and the pass through) it definitely will.
    Please use the star below the post to say thanks if we have helped !

  14. #14
    fmuzungu is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Location
    Lusaka Zamba
    Posts
    8
    I will be very honest. I've never DAO to connect to my SQL server. I always use ADO. Please help me understand. In the line:
    Set qdfpt = db.QueryDefs("passYourPassThroughStoredQuery"). What is ("passYourPassThroughStoredQuery")?

    Secondly, shouldn't the line:
    qdfpt.Connect = "ODBC;"Driver={SQL Server};Server=freeday-HP;Database=TestDB;User Id=gigida;Password=dtf666;"

    read

    qdfpt.Connect = "ODBC;""Driver={SQL Server};Server=freeday-HP;Database=TestDB;User Id=gigida;Password=dtf666;"

  15. #15
    Minty is online now Hacker
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    808
    "passYourPassThroughStoredQuery" is simply a stored pass through query in your access front end, call it whatever you think is appropriate

    This was a typo it should be;
    qdfpt.Connect = "ODBC;Driver={SQL Server};Server=freeday-HP;Database=TestDB;User Id=gigida;Password=dtf666;"

    However you can manually edit the connection string in the saved query to get it right, and test running it manually with a hard coded value whilst your are debugging.

    Please use the star below the post to say thanks if we have helped !

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

Similar Threads

  1. Replies: 9
    Last Post: 04-12-2018, 12:10 PM
  2. Executing a stored procedure under another windows user
    By system243trd in forum Programming
    Replies: 1
    Last Post: 03-16-2016, 08:29 AM
  3. Stored Procedure in MS Access 2007
    By sels1987 in forum Access
    Replies: 1
    Last Post: 05-13-2012, 11:23 AM
  4. Executing Oracle Stored Procedure
    By gsurfdude in forum Programming
    Replies: 0
    Last Post: 03-01-2011, 08:23 AM
  5. Wait for query to finish
    By MikeDS in forum Programming
    Replies: 1
    Last Post: 01-28-2011, 09:17 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
  •  
Tech Forums: Microsoft Office Forums