Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    57

    Unanswered: Stored Procedure Execution Question

    I have an Access Project that is being used to run reports off a SQL database. One of the command buttons in the app opens an ADO connection to the databse and executes a stored procedure which populates a table used to report off. This stored procedure takes some time to execute and I need to know how to determine when it is done in order to launch the report at the proper time. Does anyone know how to control the report launch so that it will not launch until the SP execution is over?

    Thanks in advance.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Depending on the SQL Server version you have, you can view the execution times by viewing the: View - SQL Server Profiler and see what process are taking up a lot of execution time and playing around with the selections, determine the time of executioning certain events. It might help on determining when to launch your report. Other than than, possibly a DTS package which runs commands in a sequence but I doubt this is what you're looking for.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Surely this is synchronous?
    Code:
    CREATE PROC usp_StopForABit
    AS
     WAITFOR DELAY '000:00:05'
    GO
    Code:
    Sub Wait()
        Dim objCom As New ADODB.Command
        Dim adoCon As New ADODB.Connection
     
        adoCon.ConnectionString = "Provider=sqloledb;Data Source=(Local);Initial Catalog=MyDB;Integrated Security=SSPI;"
        adoCon.Open
     
        objCom.CommandText = "dbo.usp_StopForABit"
        objCom.CommandType = adCmdStoredProc
        Set objCom.ActiveConnection = adoCon
     
        Debug.Print "Calling at " & Format(Now, "long time")
     
        objCom.Execute
     
        Debug.Print "Finished at " & Format(Now, "long time")
     
    End Sub
    How come your code is moving on before the sproc has executed?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •