Excel: Execute SQL Procedure with VBA -- allow user to continue working -- do not hang the Excel Application

Question

I have several methods of executing a stored procedure from Excel VBA. In fact, I am not even interested in returning the results of that procedure back to Excel, I just need to kick off the procedure. Though, when the procedure is running Excel "hangs" and because unusable to the customer, especially when the procedure run time is 5 minutes or more.

I looked at other solutions where people instantiate a new application within a single workbook, but it wasn't clear how to execute the procedure in that context. Another solution, was opening a second workbook in the background (hidden to the user), and auto-running the macro when that workbook opens, but it's not clear to me on when I know to close it.

My code for calling the procedure, note, this suffers from a timeout issue, any thoughts on that appreciated too:

Sub Workbook_Open()

 'Create connection string variable
    Dim item As String
    Dim con As New ADODB.Connection

    'Build the connection string
    con.ConnectionString = "Provider=SQLNCLI11;" _
             & "Server=MyServerName;" _
             & "Database=Oracle;" _
             & "Integrated Security=SSPI;"

   'Open the connection
    con.Open

    item = vbNullString

    item = "exec dbo.SP_ORACLE_PULL"
    con.Execute (item)

   'Close the connection   
    con.Close

 End Sub

Does anyone have any recommended approaches? Is it best to open a workbook hidden in the background and run the procedure from there? how would I know when to close that workbook and relay the status back to the originating workbook? Will that still "hang" the customers all open Excel workbooks. I searched all over and I did not find any code examples for this type of scenario.

This is all a stopgap until we can formalize a more enterprise solution in 4-6mo.

duplicate of stackoverflow.com/questions/17574524/adodb-query-timeout — Doug Coats
– Doug Coats, Commented May 15, 2019 at 17:10
@DougCoats how does a timing-out query relate to running the command asynchronously? I'd submit the answer on this post as a dupe target... which is essentially what Tim answered below. — Mathieu Guindon
– Mathieu Guindon, Commented May 15, 2019 at 17:48
@MathieuGuindon b/c i didnt read it thoroughly. Or b/c i said so (which while having a little bit of sass isnt helpful at all despite me trying to play it off). — Doug Coats
– Doug Coats, Commented May 15, 2019 at 18:03

Tim Williams · Accepted Answer · 2019-05-15 17:18:34Z

2

ADO supports asynchronous operations if you declare your Connection etc using WithEvents

See here for example: https://support.microsoft.com/en-us/help/190988/how-to-open-ado-recordsets-asynchronously-using-withevents

answered May 15, 2019 at 17:18

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel: Execute SQL Procedure with VBA -- allow user to continue working -- do not hang the Excel Application

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related