Excel – VBA – Execute stored procedure from VBA without waiting for completion

SCENARIO

I am trying to execute an SQL stored procedure from within VBA. I have been successful in doing this but my [gs stored procedure] can take up to 5 minutes to complete. During this time Excel will appear frozen to the user. I would like to execute the procedure and continue on through my code without waiting for the procedure to complete. Is this possible? Thanks in advance.

Here's my existing code:

Public Sub connect()

Dim Cnxn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim strCnxn As String
strCnxn =3D "Dsn=3DApropos3b;" & _
"Driver=3D{INFORMIX 3.30 32 BIT};" & _
"Server=3Don_b;" & _
"Service=3D1901;" & _
"Protocol=3Donsoctcp;" & _
"Database=3Datb;" & _
"UID=3Dinformix;" & _
"PWD=3DUniteK;"
Cnxn.Open strCnxn
With cmd
.ActiveConnection =3D Cnxn
.CommandText =3D "sp_otb_rpt()"
.CommandType =3D adCmdStoredProc
.Execute
End With

SOLUTION

You can use the AsynchExecute option to execute the [gs stored procedure] asynchronously.

Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
cmd.ActiveConnection = "DSN = test"
cmd.CommandTimeout = 180
cmd.CommandText = "sp_name"
cmd.CommandType = adCmdStoredProc
cmd.Execute , , adAsyncExecute
'If you don't want the user doing anything else while the query is executing
Do While cmd.State = adStateExecuting 'or 4
DoEvents
'Options here include a timer to update the form or putting a cancel button on the sheet
'so the application does not appear to be frozen.
Loop
SOURCE

LINK (Visualbasic.ittoolbox.com)

LANGUAGE
ENGLISH

1 thought on “Excel – VBA – Execute stored procedure from VBA without waiting for completion”

Comments are closed.