How to run Stored procedure in Background through VBA

Question

The Stored Procedure is running for 15 min and freezes the Excel file. How can we run the Stored procedure in Background through VBA,let it run in the background and continue to work on the same sheet.

Does the user need to work on the sheet the procedure is running on or do other procedures need to be able to run on said sheet? — Nacorid, Jun 03 '19 at 10:17
the sheet should not be freezed while stored Procedure is running — Ajinkya Lotankar, Jun 03 '19 at 10:30
Look into [DoEvents](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/doevents-function) — Tim Stack, Jun 03 '19 at 10:37
If your using ADO it supports async querying, E.g. https://stackoverflow.com/questions/16167478/executecomplete-adodb-connection-event-not-fired-with-adasyncexecute-parameter — Alex K., Jun 03 '19 at 10:48
You really need to show your code. I assume you are executing a SQL Server stored procedure. It depends on whether it needs to return data. — , Jun 03 '19 at 12:00

score 0 · Answer 1 · answered Jun 03 '19 at 10:19

0

You can't run a macro and continue to work in the same sheet.

But you can use the code below, for speed the execution of your macro :

sub my_macro()
   Application.ScreenUpdating = False
   #Your code
   Application.ScreenUpdating = True
end sub

answered Jun 03 '19 at 10:19

Hippolyte BRINGER

792
1
8
30

score -1 · Answer 2 · answered Jun 12 '19 at 05:23

Basically, Excel and VBA are not parallel processing technologies. You will need to wait for the Macro to finish before continuing your work. You could possibly run this process as an overnight job, so everything is done for you when you com into the office in the morning. Or, you can start the process and go get lunch, coffee, etc.

Search for 'Windows Task Scheduler', and follow the prompts.

Finally, in Excel, hit Alt+F11 to bring up the VBA editor. Right click on "ThisWorkbook", click "Insert", click "Module". Double click on "Module 1". Copy in the code below:

Sub Auto_Open()

Application.DisplayAlerts = False
Application.ScreenUpdating = False


Dim con As Connection
Dim rst As Recordset

Set con = New Connection
con.Open "Provider=SQLOLEDB;Data Source=Your_Server_Name_Here;Initial Catalog=Northwind;Integrated Security=SSPI;"

Set rst = con.Execute("Exec dbo.[Ten Most Expensive Products]")

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

How to run Stored procedure in Background through VBA

2 Answers2