Showing a progress bar while connecting to MS Access Database

[abyshek]

Dear Experts,

I am creating an excel tool that connects with an MS Access database stored in a remote location. Generally, it takes around 10 seconds ( sometimes faster depending on the internet speed) to run the query and get results back to the excel sheet. Currently, the form and the excel window freeze with a circle cursor which may falsely indicate that Excel is stuck or not responding.

Is there a way to show a form with a progress bar while the connection with MS Access is made and SQL query is being run? I do not want to show "please wait" message in the Excel status bar.

I want to show a progress bar when excel processes the following lines

con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = " & db_location & "; Jet OLEDB:Database Password = 123456"
query = ....some SQL query.....
recset.open query, con, adOpenKeyset, adLockOptimistic
recset.close
con.close

I tried searching for answers, but all solutions catered to different scenarios. Any help will be much appreciated. Thank you.

 

[abyshek]

Dear Experts,

I am creating an excel tool that connects with an MS Access database stored in a remote location. Generally, it takes around 10 seconds ( sometimes faster depending on the internet speed) to run the query and get results back to the excel sheet. Currently, the form and the excel window freeze with a circle cursor which may falsely indicate that Excel is stuck or not responding.

Is there a way to show a form with a progress bar while the connection with MS Access is made and SQL query is being run? I do not want to show "please wait" message in the Excel status bar.

I want to show a progress bar when excel processes the following lines

con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = " & db_location & "; Jet OLEDB:Database Password = 123456"
query = ....some SQL query.....
recset.open query, con, adOpenKeyset, adLockOptimistic
recset.close
con.close

I tried searching for answers, but all solutions catered to different scenarios. Any help will be much appreciated. Thank you.

Disclaimer: This question has been cross-posted for a quicker help. I need help as soon as possible. Thanks a lot to the whole community for being so helpful.

 

[Fluff]

Hi & welcome to MrExcel.
Thanks for letting us know that you have cross-posted this question, but can you please supply links to your thread on those other site(s) as per board rules #13 of the Forum Rules.

 

[abyshek]

Hi & welcome to MrExcel.
Thanks for letting us know that you have cross-posted this question, but can you please supply links to your thread on those other site(s) as per board rules #13 of the Forum Rules.

Dear Moderator,

Thanks for pointing me to the Forum Rules regarding cross-posting. I have read them and they make sense. Here is the cross-posted link:

www.excelforum.com/excel-programming-vba-macros/1380270-showing-a-progress-bar-while-connecting-to-ms-access-database.html

 

[6StringJazzer]

There are two problems with what you want to do. The one that really matters is that VBA is single-threaded and the recset.open call is synchronous. That is, once this line of code starts to execute, VBA can't execute anything else until it returns. A progress bar is used when you have a loop in your code to do some work, and you update the progress bar each time through the loop.

(The second one is a moot point--even if you could display and update a progress bar, there is no way to tell how much progress is actually being made.)

The best you can do is show a modeless form that says "Query in progress". It can't show the actual progress to give the user a sense of how much is left to do.

[Also posted at cross-post location]

 

[abyshek]

There are two problems with what you want to do. The one that really matters is that VBA is single-threaded and the recset.open call is synchronous. That is, once this line of code starts to execute, VBA can't execute anything else until it returns. A progress bar is used when you have a loop in your code to do some work, and you update the progress bar each time through the loop.

(The second one is a moot point--even if you could display and update a progress bar, there is no way to tell how much progress is actually being made.)

The best you can do is show a modeless form that says "Query in progress". It can't show the actual progress to give the user a sense of how much is left to do.

[Also posted at cross-post location]

@6StringJazzer, thanks for the insights and great idea. I tried that and it worked like charm.. I used a "Connecting...." GIF image and used a Microsoft WebBrowser control to play it in a UserForm. I loaded this and my original UserForm as modeless (vbModeless) and it solved the purpose.

Thank you so much for your help.