Kick off stored proc and disconnect.

[GPO]

I have an Access 2003 adp with a main form that is basically just a
container for a few subforms, all of which are datasheet view only.

I have a button on the main form that kicks off a lengthy stored proc like so:


Private Sub RunReports(strNewRun As String)
'This will create a new series of reports or refresh the latest
'existing series of reports based on the criteria in the above tables
Dim cmdRunReports As New ADODB.Command
Dim cn As ADODB.Connection
Dim prm As ADODB.Parameter
Set cn = CurrentProject.Connection

With cmdRunReports
.ActiveConnection = cn
.CommandTimeout = 7200 'allow up to two hours for each run.
.CommandType = adCmdStoredProc
.CommandText = "sp_aggr_KPI"
Set prm = .CreateParameter("@new_run", adBoolean, adParamInput)
prm.Value = strNewRun
.Parameters.Append prm
.Execute
End With



End Sub

When I kick the stored procedure off, it seems to lock my form up until it
has stopped running. What I want though, is to be able to kick it off and
disconnect (leave it to run under its own steam). I can ascertain the
progress or otherwise of the stored proc by feedback from the datasheets in
the subforms mentioned earlier. How can I do this?

Regards

GPO

 

[Jamie Collins]

When I kick the stored procedure off, it seems to lock my form up until it
has stopped running. What I want though, is to be able to kick it off and
disconnect (leave it to run under its own steam). I can ascertain the
progress or otherwise of the stored proc by feedback from the datasheets in
the subforms mentioned earlier. How can I do this?

See:

How To Create an Asynchronous Connection in ADO
http://support.microsoft.com/kb/194960

There are better articles around, though e.g. google for
"adAsyncConnect".

Jamie.

--