Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    1

    Unanswered: How to execute a sproc without having to wait until it finishes

    I have the below code that is called from a button in Access. I would like the code to execute a stored procedure and then allow the user to continue working within the database, without having to wait around for the sproc to complete (it could take up to 8 hours to complete sometimes). Is there a way to do this? If so, what would I have to change in my code? Thanks for any help, it's appreciated!

    Private Sub cmdInitiate_Click()
    Dim Cnn As ADODB.Connection
    Dim Cmd As ADODB.Command
    Dim parm As ADODB.Parameter

    On Error GoTo Err_Initiate_Click

    Set Cnn = CurrentProject.Connection
    Set Cmd = New ADODB.Command
    Cmd.ActiveConnection = Cnn

    'Stored Procedure used to Initiate the Recall
    Cmd.CommandText = "uspNarrowProductRecall"

    'Tell ADO it will execute a stored procedure
    Cmd.CommandType = adCmdStoredProc

    'Populate and append the parameteres for stored procedure
    Set parm = Cmd.CreateParameter("PvcMaterial", adVarChar, adParamInput, 30, Me.cboMaterial)
    Cmd.Parameters.Append parm

    Set parm = Cmd.CreateParameter("PdtPullDate", adDate, adParamInput, , Me.txtPullDate)
    Cmd.Parameters.Append parm

    Set parm = Cmd.CreateParameter("PinStep", adInteger, adParamInput, , 0)
    Cmd.Parameters.Append parm

    'Run the stored procedure
    Cmd.Execute , , adExecuteNoRecords

    MsgBox "Recall Initiated"

    'Free up resources
    Set parm = Nothing
    Set Cmd = Nothing
    Set Cnn = Nothing

    Exit_Initiate_Click:
    Exit Sub

    Err_Initiate_Click:
    MsgBox Err.Description
    Resume Exit_Initiate_Click

    End Sub

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Re: How to execute a sproc without having to wait until it finishes

    Originally posted by slambert007
    I have the below code that is called from a button in Access. I would like the code to execute a stored procedure and then allow the user to continue working within the database, without having to wait around for the sproc to complete (it could take up to 8 hours to complete sometimes). Is there a way to do this? If so, what would I have to change in my code? Thanks for any help, it's appreciated!

    Private Sub cmdInitiate_Click()
    Dim Cnn As ADODB.Connection
    Dim Cmd As ADODB.Command
    Dim parm As ADODB.Parameter

    On Error GoTo Err_Initiate_Click

    Set Cnn = CurrentProject.Connection
    Set Cmd = New ADODB.Command
    Cmd.ActiveConnection = Cnn

    'Stored Procedure used to Initiate the Recall
    Cmd.CommandText = "uspNarrowProductRecall"

    'Tell ADO it will execute a stored procedure
    Cmd.CommandType = adCmdStoredProc

    'Populate and append the parameteres for stored procedure
    Set parm = Cmd.CreateParameter("PvcMaterial", adVarChar, adParamInput, 30, Me.cboMaterial)
    Cmd.Parameters.Append parm

    Set parm = Cmd.CreateParameter("PdtPullDate", adDate, adParamInput, , Me.txtPullDate)
    Cmd.Parameters.Append parm

    Set parm = Cmd.CreateParameter("PinStep", adInteger, adParamInput, , 0)
    Cmd.Parameters.Append parm

    'Run the stored procedure
    Cmd.Execute , , adExecuteNoRecords

    MsgBox "Recall Initiated"

    'Free up resources
    Set parm = Nothing
    Set Cmd = Nothing
    Set Cnn = Nothing

    Exit_Initiate_Click:
    Exit Sub

    Err_Initiate_Click:
    MsgBox Err.Description
    Resume Exit_Initiate_Click

    End Sub
    You can use WithEvents property of the ADO connection to let you know when the sproc has finished and you can use an adsynchrous connection to allow you to carry on using the database.

    You can use a command such as;
    ' place this in the module declarations area
    Private WithEvents mcnn As ADODB.Connection
    Private mcmd As ADODB.Connection

    ' To execute a sproc
    Private Sub cmdExecute_Click()
    Set mcmd = New ADODB.Command
    ' i assume you have created the connection elsewhere
    Set mcmd.ActiveConnection = mcnn
    mcmd.CommandText = "MySproc"
    mcmd.CommandType = adCmdStoredProc
    mcmd.Execute(Options:=adsyncexecute)
    End Sub

    ' when the sproc has finished this procedure will be called by ADO
    Private Sub mcnn_ExecuteComplete(...Access will fill in this bit for you)
    MsgBox "Finished"
    End Sub

    You must keep the form or class active whilst the sproc is running other wise it will be terminated. Have a look at the help file on WithEvents as it is very powerful and you can do a lot with it.

Posting Permissions

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