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.

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.

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.

And this in form view:

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.

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.