SQL Sproc Fails when Attempting to Exec Asynch from Access via ADO

[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 sprocs.

I need to run asynch as the call is from an Access Db and I want to display a progress screen. 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

<originally posted in the wrong forum, so apologies for cross-posting>

 

[timfoster]

Why do you set the connection as an object? I've written hundreds of thousands of connections from access, excel, asp etc using ado. Never once have I created a connection object.

My code aways looks like:
ConnStr = "Provider=sqloledb;" & _
"Server=MyServer;" & _
"Database=MyDB;" & _
"Integrated Security=SSPI"

cmd.ActiveConnection = ConnStr

Whether this is your problem, I couldn't say. But it would seem logical. You say that your sp calls another which in turn calls others. If the connection idles it may think it's timed out.

Is there a reason why you can't simply call the sp's direct?

 

[idbr]

Interesting, I've always used a connection object myself. Changing it doesn't solve my problem, however a bit of further investigation does.

The sproc is throwing an error within a try/catch block that isn't seemingly handled by the block. Because the exec is asynch, interestingly, this isn't reported back to the Access client.

So now a SQL problem, rather than an Access one.

Thanks, Iain