Updating the status bar at the bottom of the Access Screen

[Seti]

Is there a way as I run a series of queries through a macro, to have the stauts bar at the bottom of the Access screen show me which query (of many) in my macro is running or alternately, running query x of y?

In Excel, I can use Application.Statusbar = "My Message". I tried the same thing in Access and it appears that it is not supported unless I need to do something first to make it happen.

I currently have about 20 queries and the process takes a couple of hours and I need the user to have some idea of where he/she is in the process.

Thanks for any suggestions.

Seti

 

[CT Witter]

Re: Updating the status bar at the bottom of the Access Scre

Check out MS KB article #103404 http://support.microsoft.com/default.aspx?scid=kb;en-us;103404&Product=acc

Should get you started.

CT

 

[Seti]

Re: Updating the status bar at the bottom of the Access Scre

Thanks, that works perfectly in my test environment. Hoping the sam eis true for production. If not, I'll be back with a few details on any problems.

Thanks again for the find.

 

[Seti]

Re: Updating the status bar at the bottom of the Access Scre

The tip mentioned above worked fine in my test environment, but when I went live, I found a problem. I am running about 15 queries and all told they take about 30 minutes to run. I built the progress bar code and implemented it. But, when I run my macro, it initializes the progress bar and then it seems like the query blanks out the status bar and I lose my progress bar until the query ends, then my progress bar updates for a second and then the next query blanks it out again. Does anyone have any ideas? Thanks.

Here is the code I am using:

Option Compare Database
Function OpenBar()
    Dim RetVal As Variant
    RetVal = SysCmd(1, "Progress Meter", 5)
End Function

Function CloseBar()
    Dim RetVal As Variant
    RetVal = SysCmd(3)
End Function

Function UpdateBar(NumDone As Integer)
    Dim RetVal As Variant
    RetVal = SysCmd(2, NumDone)
End Function

I then use the RunCode command in my macro to run the appropriate functions.

Seti

 

[Seti]

Re: Updating the status bar at the bottom of the Access Scre

Bump

 

[SydneyGeek]

Re: Updating the status bar at the bottom of the Access Scre

Just curious ... what are you running that requires over 6 minutes per query, in strings of 20 queries? Are we talking huge numbers of records? Slow network connection to linked tables? I think it's possible to reduce the overall query time with a bit of planning -- give us some clues!

Denis

 

[Seti]

Re: Updating the status bar at the bottom of the Access Scre

There are huge tables with multiple links between each table. There are many intense calculations being performed that update existing tables. I have queries calling other queries. While I might be able to improve overall efficiency, I was hoping that I could get the progress bar working correctly. Even if I improve efficientcy 50%, it will still take 30-40 minutes on my clients PCs.

 

[bat17]

I think that you will need to create a custom bar in a form as Access will always take back controll of the status bar when a query runs.

Peter

 

[bat17]

And in anticipation of your next question Try this Article

peter

 

[Seti]

Re: Updating the status bar at the bottom of the Access Scre

Thanks, I'll look into the link and see if I can use it. Appreciate the help.