3

I have a progress bar that updates based on the line:

Call ProgressBar(X)

Where X indicates the percentage that the bar displays as 'complete'.

I've roughly calculated various time intervals throughout the code and placed the line several places throughout. It's at the point where it runs quite smoothly for the majority of the code but only half of the bar, with the problem being a forced jump from 10% to 60%.

I'm using an ADODB connection to run a SQL query in the code (I can't take it out of the code because I'm passing variables through it). The jump from 10 to 60 is either side of the line where I'm executing the query 

 Set rs = conn.Execute(QryND)

where rs is defined as ADODB.Recordset and conn as ADODB.Connection.

I guess ideally what I'm after would be to know if it's possible to say:

       Call ProgressBar(10)
        'code to the effect of: "in x seconds, execute the next line but in   
    'the meantime continue with the code

            Call ProgressBar(20)
        'code to the effect of: "in 2x seconds, execute the line but in the 
'meantime continue with the code
            Call ProgressBar(30)
            Set rs = conn.Execute(QryND)

Or something to that effect.

Alternatively a means of running the query in the background and continuing the code up to a point. Eg

     Call ProgressBar(10)
'instruct to run in backrgound:
    Set rs = conn.Execute(QryND) 
 Call ProgressBar(10)
'wait x seconds
 Call ProgressBar(20)
'wait x seconds
.
.
.

'Stop running query in background (in case it hasn't finished)

Do either of these sound possible?

user2842721
  • 125
  • 4
  • 13
  • ADO supports evented async execution, if you think its worth the hassle to implement: https://support.microsoft.com/en-us/kb/190988 – Alex K. Apr 26 '15 at 13:34

1 Answers1

-1

Running background queries is not recommended as they can run your code into errors. What you can actually do is leave the StatusBar the f* alone and check it with while loops (or at regular intervals) , i.e. does it fetch the connection, run or is it finished.
What you can do is create an ActiveX object to display whatever you wanted to tell, even up to fancy load bars and multiline feedback.
You cannot really have asynchronous processes in a single-threaded application unless you call outside scripts to execute them.

user3819867
  • 1,114
  • 1
  • 8
  • 18
  • Sorry, I don't think I'm following you. Could you elaborate on 'check it with while loops (or at regular intervals)'? I'm not sure how I can do this while one line of code is processing. – user2842721 Apr 26 '15 at 12:57
  • Damn, I'm sorry. I didn't think only user created texts could be read. What you probably want to have is like [this](http://stackoverflow.com/a/5181347/3819867). If you could check either the statusbar or the query refresh status (from which the statusbar itself refreshes) that would be your solution. Unfortunately my brief search wasn't quite successful. Edit: I found the [QueryTable.Refreshing Property on msdn](https://msdn.microsoft.com/en-us/library/office/ff834459.aspx). – user3819867 Apr 26 '15 at 14:07
  • The continue the code but run the statusbar update can be done by the [Application.OnTime method](https://msdn.microsoft.com/en-us/library/office/ff196165.aspx). Note: you will want to end given sub for it to run. Then it can call a second portion of your code etc. – user3819867 Apr 26 '15 at 16:55