Heelp Book 2011 Excel VBA Execute stored procedure

Heelp. Book © - 2011 Excel – VBA Execute stored procedure from VBA without waiting for completion Source: Link Posted By: Heelp. Book Permalink: Link Slide: 1 19/05/2021 How-Tos <<

Heelp. Book © - 2011 SCENARIO I am trying to execute an SQL stored procedure from within VBA. I have been successful in doing this but my 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. Slide: 2 >> Execute stored procedure from VBA without waiting for completion 19/05/2021 How-Tos <<

Heelp. Book © - 2011 Here's my existing code: Public Sub connect() Dim Cnxn As New ADODB. Connection Dim cmd As New ADODB. Command Dim str. Cnxn As String Slide: 3 >> Execute stored procedure from VBA without waiting for completion 19/05/2021 How-Tos <<

Heelp. Book © - 2011 str. Cnxn =3 D "Dsn=3 DApropos 3 b; " & _ "Driver=3 D{INFORMIX 3. 30 32 BIT}; " & _ "Server=3 Don_b; " & _ "Service=3 D 1901; " & _ "Protocol=3 Donsoctcp; " & _ "Database=3 Datb; " & _ "UID=3 Dinformix; " & _ "PWD=3 DUnite. K; " Cnxn. Open str. Cnxn Slide: 4 >> Execute stored procedure from VBA without waiting for completion 19/05/2021 How-Tos <<

Heelp. Book © - 2011 With cmd. Active. Connection =3 D Cnxn. Command. Text =3 D "sp_otb_rpt()". Command. Type =3 D ad. Cmd. Stored. Proc. Execute End With Slide: 5 >> Execute stored procedure from VBA without waiting for completion 19/05/2021 How-Tos <<

Heelp. Book © - 2011 SOLUTION You can use the Asynch. Execute option to execute the stored procedure asynchronously. Dim cmd As ADODB. Command Set cmd = New ADODB. Command Slide: 6 >> Execute stored procedure from VBA without waiting for completion 19/05/2021 How-Tos <<

Heelp. Book © - 2011 cmd. Active. Connection = "DSN = test" cmd. Command. Timeout = 180 cmd. Command. Text = "sp_name" cmd. Command. Type = ad. Cmd. Stored. Proc cmd. Execute , , ad. Async. Execute Slide: 7 >> Execute stored procedure from VBA without waiting for completion 19/05/2021 How-Tos <<

Heelp. Book © - 2011 'If you don't want the user doing anything else while the query is executing Do While cmd. State = ad. State. Executing 'or 4 Do. Events '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 Slide: 8 >> Execute stored procedure from VBA without waiting for completion 19/05/2021 How-Tos <<

Heelp. Book © - 2011 That’s all Folks Come visit us on www. heelpbook. net to find and read more documents and guides… AND / OR { Subscribe to our feed RSS: Link Or see&read us on Feed. Burner: Link Slide: 9 >> Execute stored procedure from VBA without waiting for completion 19/05/2021 How-Tos <<