Progress Bar for MS Access

I know this one has been hashed over a few times, but all of the examples I’ve seen are either really clunky to use, or rely on an ActiveX control. I needed to create a progress bar that was native to Access and easy to use.

Start with creating a blank unbound form.

BlankForm

Go to design mode and resize the detail area and add a rectangle named boxProgress and a text box named txtStatus as shown below. Leave just a little bit of space around your rectangle. Set the “Back Style” property of the text box to “Transparent”. You’ll also need to make sure that the Form Property “Pop Up” is set to “Yes”.  Make sure the control names are set properly, the code relies on those names.

designMode

Ok. Are you ready for the hard part? You need to adjust the size of the frame of the form to display just the right amount of the detail area. It needs to actually be smaller than the detail area when you’re in design mode. This will take a few tries to get right. Just keep at it, you’ll get it right eventually. It will look something like this in design mode.

designMode2

And this in form view:

FormView

Now that we have a form to work with, we’ll need to add some code behind it. This whole thing only works because Access Forms are glorified class modules. We can do things like add properties and call new instances of forms. All of the code I will share with you is based on this fact. Go ahead and paste the code below into the vbe under your new progress bar.


Option Compare Database
Option Explicit

' ***************************************************************
' * Class: Form_ProgressBar *
' * Popup progress bar *
' * *
' * Author: Christopher J. McClellan *
' * Published under Creative Commons Attribution-Share Alike *
' * http://creativecommons.org/licenses/by-sa/3.0/ *
' * You are free to change, distribute, and pretty much do *
' * whatever you like with the code, but you must give credit *
' * to the original author and publish any derivitive of this *
' * code under the same license. *
' ***************************************************************
Private Const MaxBoxWidth As Long = 7200 'maximun boxProgress width

Public Enum ePBarModeType
 PBarMode_Percent = 0
 PBarMode_Executing = 1
End Enum

Private mMode As ePBarModeType
Private mCurrentProgress As Long
Private mSteps As Long

Public Property Get PercentComplete() As Double
'read only
 PercentComplete = mCurrentProgress / mSteps * 100
End Property

Public Property Let Mode(PBarMode As ePBarModeType)
 mMode = PBarMode
End Property

Public Property Get Mode() As ePBarModeType
 Mode = mMode
End Property

Public Property Let CurrentProgress(lng As Long)
' Updating the CurrentProgress property updates the status of the Progress Bar

mCurrentProgress = lng
 ' format #0 makes a 1 or 2 digit number without decimals
 If mMode = PBarMode_Percent Then
 'format "#0" gives a 1 or 2 digit integer
 Me.txtStatus = Format(Me.PercentComplete, "#0") & " % Complete"
 ElseIf mMode = PBarMode_Executing Then
 Me.txtStatus = "Executing..."
 End If
 ' boxProgress.Width = a percentage of maximum box width
 Me.boxProgress.Width = (mCurrentProgress / mSteps) * MaxBoxWidth
 Me.Repaint
 DoEvents
End Property

Public Property Get CurrentProgress() As Long
' current step of process
 CurrentProgress = mCurrentProgress
End Property

Property Let steps(lng As Long)
' total number of steps to process
 mSteps = lng
End Property

Public Sub init(steps As Long, Mode As ePBarModeType, Optional strCaption As String = "Loading...")
' initializes values for progress bar

Me.Mode = Mode
 Me.Caption = strCaption
 mCurrentProgress = 0
 mSteps = steps
 Me.txtStatus = "Ready"
 Me.boxProgress.Width = 0
 Me.Visible = True
End Sub

Now let’s create a new module and use our new progress bar. We’ll create a new instance of our progress bar like we would any other class object. Set pbar = New Form_ProgressBar (or whatever you happened to name your form). In the first example, we’ll use it in a simple for loop.

</pre>
Private Sub exampleCall1()
' example call for using progress bar with a looping process
 Dim pbar As Form_ProgressBar
 Dim i As Long
 Dim steps As Long
 steps = 100000

 ' create new instance of Progress Bar
 Set pbar = New Form_ProgressBar
 With pbar
 ' #of steps, Mode, Caption
 .init steps, PBarMode_Percent, "Hey, I'm working here!"
 For i = 1 To steps
 ' do something in a loop

 ' update progress
 .CurrentProgress = i
 Next i
 End With
 Set pbar = Nothing
End Sub

Running example call 1 will result in a progress bar that looks like this.

PerentProgress


Private Sub exampleCall2()
' example call for using progress bar with an adodb.command
 Dim cn As New ADODB.Connection
 Dim cmd As New ADODB.Command
 Dim prm As ADODB.Parameter

 With cn
 .Provider = "Microsoft.Access.OLEDB.10.0"
 .Properties("Data Provider").Value = "SQLOLEDB"
 .Properties("Data Source").Value = "serverName"
 .Properties("User ID").Value = "userName"
 .Properties("Password").Value = "password"
 .Properties("Initial Catalog").Value = "databaseName"
 .CommandTimeout = 500
 .ConnectionTimeout = 500
 .Open
 End With

 With cmd
 .ActiveConnection = cn
 .CommandType = adCmdStoredProc
 .CommandTimeout = 0
 .CommandText = "dbo.uspSomeStoredProcedure"
 End With

 'must execute command asynchronously in order to show progress bar while remote command executes.

' otherwise, the vba code freezes until the command completes.
 cmd.Execute , , adAsyncExecute

 Dim pbar As New Form_ProgressBar
 Dim i As Long
 pbar.init 10000, PBarMode_Executing, ""

 Do While cmd.STATE = adStateExecuting
 For i = 0 To 10000
 pbar.CurrentProgress = i
 Next i
 Loop
 Set pbar = Nothing

 DoCmd.Hourglass False

End Sub

Example call 2 will result in a progress bar that infinitely loops while displaying an “Executing…” message.  Enjoy.

 

UPDATE:

I’ve posted the actual form and code to Google Drive. You can now download the file.

Advertisements

, , ,

  1. #1 by Daniel Wishart on June 10, 2014 - 12:25 pm

    I get a compile error when running the sub. It seems the ADODB.Command variable is causing the problem. Any ideas?

    Like

    • #2 by Christopher J. McClellan on June 10, 2014 - 1:58 pm

      You need to add a reference to the Microsoft ActiveX Data Objects 6.1 Library Daniel. I’m not looking at the code right this second, but the progress bar doesn’t rely on it. Just the example call does. You could also just remove the example call from the code.

      Like

  2. #3 by Rob Hoogland (@robhoogland) on May 16, 2015 - 8:58 pm

    Hi Christopher, it took me some time to get it to work in my existing module in my database. But it look great. I’m still a noob with VBA, but is it possible to let the progress bar decrease to the right side? Then with the color of ProgressBar set to white you can put a image behind it to simulate a multicolor progress bar (window 7 sty

    Like

    • #4 by Christopher J. McClellan on May 16, 2015 - 10:27 pm

      I’m sure it’s possible. Yes. Been a while since I looked at that code though. You would have to adjust both it’s width and position (Left) to make it scroll from the right I believe. Mind if I ask why you’d want it to do that? I’ve never seen a progress bar go from right to left. Or do you mean go from 100% to 0? In that case, I think you could just start at max width and decrease it each time you set the current progress. A bit of a hack, but should work ok. Comment your code well or it probably won’t make sense 6 months from now.

      Like

  3. #5 by Rob Hoogland (@robhoogland) on May 18, 2015 - 4:39 am

    Hi Christopher, just an hour after my comment I found the solution myself. I made the following changes to your code:
    ‘ boxProgress.Width = a percentage of maximum box width
    Me.boxProgress.Left = (mCurrentProgress / mSteps) * MaxBoxWidth
    Me.boxProgress.Width = MaxBoxWidth – ((mCurrentProgress / mSteps) * MaxBoxWidth).
    The reason I asked was because I saw the progress bar in Outlook which isn’t just plain green but has a sort of glow/shine over it. I just wanted it to reproduce that.
    With a white progress bar decreasing from right to left, with a image of the Outlook progress bar behind the boxProgressBar, I can visually achieve that.
    Ofcourse there would be a simpler way, but for now I’m happy.

    Like

  4. #8 by Robert Shaw on June 3, 2015 - 2:17 pm

    I have a number of pass-through queries to a SQLServer that can take several minutes. Although I turn on and off the Hourglass, I like the idea of a continuous loop progress bar. I use DAO and prefer not to change to ADO on all my routines. Since there is not a Connection.State equivalent in DAO, any thoughts on how to modify the Do While cmd.STATE = adStateExecuting to a DAO compatible process?
    Thank you.

    Like

    • #9 by Christopher J. McClellan on June 3, 2015 - 3:11 pm

      You know, that’s a really good question. Honestly, I really just don’t know enough about DAO to say. What I can tell you is that my implementation of the continuous loop hinges on executing the command object asynchronously. I’d start with looking into if DAO is capable of doing the same. If it can’t execute async, the pass through will block the UI and it won’t work. If you can execute async though, you should be able to a similar pattern to ExampleCall2.

      Like

      • #10 by Robert Shaw on June 3, 2015 - 6:25 pm

        There are DAO method/properties dbRunAsync and .StillExecuting which seem viable options, but in 2010 the ODBCDirect object is no longer supported for external databases. MSDN states to use ADO. QueryDef.Execute supports dbRunAsync but does not support SELECT queries only action queries. I seem to be back to the starting place.

        Thanks for your comments.

        Like

    • #11 by Christopher J. McClellan on June 3, 2015 - 7:58 pm

      Sounds like it. If you’re already using a passthrough query, it shouldn’t be hard to convert to ADODB, depending on your needs. Some parts of Access just doesn’t function well on ADODB to be honest about it.

      Like

  5. #12 by Wim Van Geyt on October 15, 2015 - 9:41 am

    Hi, I am getting an error on the custom type ePBarModeType? Where do you define this type?

    Like

  6. #14 by George Karagiannakis on November 23, 2015 - 11:17 am

    When you say “Go ahead and paste the code below into the vbe under your new progress bar.” what do you mean? Where is exactly this? Insert the code as a new Class in VB Editor?

    Like

    • #15 by Christopher J. McClellan on November 23, 2015 - 12:24 pm

      In the form’s code behind.

      Like

      • #16 by George Karagiannakis on November 23, 2015 - 5:25 pm

        Thank you very Christopher for your code and your immediate response. I have a search form with a text and a button. The user insert keyword in the text box and when the button is clicked a query is running through the onClick event in Access.
        I would like to ask you how can I include the code from example 1 code into this event. I tried to put the sql query into the loop after the for statement but it was very slow and I think it was not correct.

        Like

      • #17 by Christopher J. McClellan on November 23, 2015 - 6:11 pm

        If you want to show “progress” on a query, you’ll need to use example call 2, but it only works with connecting to an ADODB provider that supports async. Note that it won’t show actual progress, but just an indication that the program is doing something.

        Like

  7. #18 by Secretary JJNH on August 16, 2016 - 12:28 am

    Thank you very much. Your code worked perfectly and was a great aid to my project. Thanks a Ton.

    Liked by 1 person

  8. #20 by Mario Mirandola on April 6, 2017 - 8:40 pm

    This is exactly what I have been looking for for quite a while to monitor the progress of a pretty massive, time consuming macro that I have. I have to apologize that I am very limited in my VBA/Coding skills. How do we link what you gave us to the macro or query we want to utilize this on?

    Like

    • #21 by Christopher J. McClellan on April 9, 2017 - 11:14 pm

      That depends on what kind of query you have/what kind of work you’re doing in the background. The progress bar supports two different modes. Which you use depends on how you execute the query.

      Like

      • #22 by Mario Mirandola on April 11, 2017 - 7:46 pm

        I have a macro that calls a series of update queries that update numerous tables. To make this as simple as possible how do I set it up to monitor a single update query that is called from a macro?

        Like

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: