Showing a progress bar while connecting to MS Access Database

[abyshek]

Cross-posted at https://www.mrexcel.com/board/thread...abase.1208095/

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

Please Login or Register  to view this content.

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.

[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]

[mogul]

I looked at this years ago. There is no direct way to do this. In general, ping time is a major factor in time? It greatly depends if the machine is loaded. I looked at other servers like PostgresSQL and SQL Server. They worked a little better but Access did surprisingly well.

[abyshek]

Thank you both for your replies. 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.

[6StringJazzer]

Glad you were able to take my idea and turn it into your needed solution. Thanks for the rep.

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."