-2

Edited: Is it good practice to have a Store Procedure running retrieving data while the user keeps working while that data is compiled for them?

Old Question: I run a stored procedure to retrieve data from a server that takes about 10 - 15 minutes to complete. While running the stored procedure, my Excel workbook freezes until all the data has been returned. I might have an option to run this stored procedure in the background while allowing the Excel workbook to be workable. The idea is to give the user the possibility to work in the workbook while retrieving data instead of waiting 10 - 15 minutes just looking at the worksheet.

My question: I have never seen this idea before in any other application. I don't know if this means a good practice standard. Having the database loaded while working in the same workbook and when the data finishes alert the user that all the data has come through.

What do you guys think? Do you believe this could complicate something else?

I don't really like the idea but at lest the user wouldn't lose 10 - 15 minutes.

Thank you all :)

Alex Martinez
  • 201
  • 1
  • 9
  • What is your question and why do need `VBA` to solve it? – Skip Intro Dec 12 '16 at 16:26
  • Because VBA is my based language to work. I call the stored procedure through VBA and I manipulate the data also using VBA. The question is if is a good practice to call a store procedure, let the user work in the workbook while waiting for the data, once the retrieving process is complete, stop the user of doing what is was doing to manipulate the data that came through. – Alex Martinez Dec 12 '16 at 16:36
  • http://stackoverflow.com/help/dont-ask – Skip Intro Dec 12 '16 at 17:01
  • Exactly how are you running your stored procedure? If you're using ADO then you can do that asynchronously and capture the events: http://stackoverflow.com/questions/16167478/executecomplete-adodb-connection-event-not-fired-with-adasyncexecute-parameter – Tim Williams Dec 12 '16 at 18:04

1 Answers1

0

You need to create an instance of Excel application and run your stpred procedure there. In that case, your Excel application will still be available for the user, however I am expecting it to be slow. You have to modify your VBA code then to run in under a new instance of Excel application:

dim xlApp as Excel.Application
set xlApp=New Excel.Application

so here after to do your stuff you have to mention the application too since you have now more than one active application. For example to create a new workbook you would do:

xlApp.Workbooks.Add

I think you can probably open a new instance of Excel manually, then open your workbook that contains your VBA code using OPEN from FILE tab and then run your code, in that case the default Excel application will be available for the user to work. To open a new instance of Excel keep SHIFT key down and click on your Excel application icon (on your start menu or desktop etc, if you don't know where you can find it, simply go to Windows Start Menu and Search EXCEL)

Hope this helps!

Ibo
  • 4,081
  • 6
  • 45
  • 65