So I'm not to concerned about optimizing the time per se but simply to let the end user know it hasn't locked up. Any database has issues if it can't make use of indexes, for example. But you can always show the hour glass; this tells the user that something is happening. I'd like to know the progress of the current query but the JET DB engine doesn't update the parent thread so it seems impossible. End Select End Property Public Property Let Hide Caption(By Val bln Hide As Boolean) On Error Go To 0 ' Debug Mode mbln Hide Cap = bln Hide Exit Property Err Handler: Select Case Err. Repaint ' may not be needed ' Use white or black, depending on progress If Me. Do Events mdte Last Update = Now End If Else Do Events End If Exit Sub Err Handler: Select Case Err.
Access is fine with a mere 100,000 records, unless they are very large records indeed. Hoping I'm wrong here..., and use it like this: This produces a nice little progress bar: On your form, all you need is three labels. Number Case Else Log Err Err, "cls Lbl Prog", "Hide Caption", Erl Resume Next ' Resume at next line. Number Case Else Log Err Err, "cls Lbl Prog", "Update", Erl Resume Next ' Resume at next line.
You can't add a progress bar to the process of running a single query, because it's an "atomic" action in Access. Set the back label to the desired size and make the other two hidden. And here is the code for Option Compare Database Option Explicit ' By Adam Waller ' Last Modified: 12/16/05 'Private Const sng Offset As Single = 1.5 ' For Excel Private Const sng Offset As Single = 15 ' For Access Private mdbl Max As Double ' max value of progress bar Private mdbl Val As Double ' current value of progress bar Private mdbl Full Width As Double ' width of front label at 100% Private mdbl Inc Size As Double Private mbln Hide Cap As Boolean ' display percent complete Private mobj Parent As Object ' parent of back label Private mlbl Back As Access. End Select End Property Private Sub Update() On Error Go To 0 ' Debug Mode Dim int Percent As Integer Dim dbl Width As Double 'On Error Resume Next int Percent = mdbl Val * (100 / mdbl Max) dbl Width = mdbl Val * (mdbl Full Width / mdbl Max) mlbl Front. End Select End Sub Public Sub Increment() On Error Go To 0 ' Debug Mode Dim dbl Val As Double dbl Val = Me.
Label ' label created for front Private mlbl Caption As Access. End With ' set properties for front label With mlbl Front mdbl Full Width = mlbl Back. A border, and solid bar that is resized as things progress. To use If pbar Is Nothing Then Set pbar = New p Bar_sub Me. Progress Bar_Bar End If pbar.value = 0 = 145 ' number of interations ... Move Next Loop One can associate a status line with the progress bar that announces what element is being processed. District Some Where, sales agent Whom Ever ======== Progress Bar substitute p Bar_sub ============== Option Compare Database Option Explicit Dim position As Long Dim maximum As Long Dim increment As Single Dim border As Object Dim bar As Object Sub init(rect As Object, b As Object) Set border = rect Set bar = b bar.width = 0 hide End Sub Sub hide() bar.visible = False border.visible = False End Sub Sub show() bar.visible = True border.visible = True End Sub Property Get Max() As Integer Max = maximum End Property Property Let Max(val As Integer) maximum = val increment = border.width / val End Property Property Get value() As Integer value = position End Property Property Let value(val As Integer) position = val bar.width = increment * value End Property Use the command Do Events after updating the progressbar (ac Sys Cmd Update Meter).
Label ' progress bar caption Private mdte Last Update As Date ' Time last updated Private mbln Not Smooth As Boolean ' Display smooth bar by doevents after every update. Label) On Error Go To 0 ' Debug Mode Dim obj Parent As Object ' could be a form or tab control Dim frm As Form Set mobj Parent = Back Label. In case of a large number of records only execute Do Events every x times since this slows down your application a little.
' This class displays a progress bar created ' from 3 labels. Parent ' set private variables Set mlbl Back = Back Label Set mlbl Front = Front Label Set mlbl Caption = Caption Label ' set properties for back label With mlbl Back .
Performance degrades significantly as the database size increases. Finally, starting with an Access database has tempted many developers to do a dangerous thing.
Sometimes a single-user application becomes popular enough that there's a desire for it to be used by multiple simultaneous users.