0

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.

Stavros Jon
  • 1,695
  • 2
  • 7
  • 17
user3113282
  • 1
  • 2
  • duplicate of https://stackoverflow.com/questions/17574524/adodb-query-timeout – Doug Coats May 15 '19 at 17:10
  • 1
    Possible duplicate of [ADODB query timeout](https://stackoverflow.com/questions/17574524/adodb-query-timeout) – Doug Coats May 15 '19 at 17:10
  • @DougCoats how does a timing-out query relate to running the command asynchronously? I'd submit the answer on [this post](https://stackoverflow.com/q/41837242/1188513) as a dupe target... which is essentially what Tim answered below. – Mathieu Guindon May 15 '19 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 May 15 '19 at 18:03

1 Answers1

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

Tim Williams
  • 154,628
  • 8
  • 97
  • 125