Problem with Asynch ADO SQL Sproc Exec

[idbr]

Hi,

I'm having an issue with the following procedure:

Public Sub Exec_Asynch_ADO(strProcName As String, intScenarioId As Integer)

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim strConn As String

' revised connection string post deployment
strConn = "Provider=sqloledb;" & _
            "Server=MyServer;" & _
            "Database=MyDB;" & _
            "Integrated Security=SSPI"

'----------------------------------------------
' open the connection and execute the procedure
'----------------------------------------------

Set conn = New ADODB.Connection
conn.Open strConn

Set cmd = New ADODB.Command
cmd.ActiveConnection = conn

cmd.CommandType = adCmdStoredProc
cmd.CommandText = strProcName
cmd.Parameters.Append cmd.CreateParameter("@scenario_id", adInteger, adParamInput, 36, intScenarioId)

cmd.Execute , , adAsyncExecute + adExecuteNoRecords

'--------
' cleanup
'--------

conn.Close

Set cmd = Nothing
Set conn = Nothing

End Sub

When I run with the adAsyncExecute parameter, it stops the procedure executing properly, possibly as a result of closing the connection? Although commenting the conn.Close line give sporadic results - sometimes it runs, sometimes not. Besides which, I don't want to be leaving loads of stray connections hanging around.

The procedure being called is a one line sproc that executes another procedure in turn, which executes a whole bunch of other procs.

I need to runn asynch as the call is from an Access Db and I want to display a progress meter, without the asynch, the progress sub I've written doesn't fire until the sproc is done. Which is kind of pointless...

Any suggestions?

Thanks, Iain

 

[idbr]

Oops, wrong forum - my bad.

Moved this to the Access Modules forum instead.

Ta, Iain