Progress Bars

[ianclegg]

Is it possible to show a progress meter in the status bar whilst running some sql (using syscmd acsyscmdinitmeter etc, if so how?

 

[The_Doc_Man]

Basically, no.

The SQL query is treated as a monolithic event. I.e., Access does not give you a response until the query is complete. In order to update a status bar, you need to have separable events.

Now, that is technically true. But if you are writing code, there is a chance (not a good one) that you could make a recordset out of the query and process it one step at the time... That would give you the ability to do status-bar updates. But I've never tried this for an action query, only for SELECT queries. So I'm making no bets.

 

[Surjer]

Doc Man,

You it just updates the Progress bar when it is finished.

The following code works in VB6.0 but not in access....

Private Sub CmdCodeSwaper_Click()
Dim db As Database
Set db = CurrentDb

Dim rs As Recordset 'codelinks
Dim rs2 As Recordset 'final
Dim i As Integer
Dim reccount As Integer

i = 0

Set rs = db.OpenRecordset("Select * from code_link")

Set rs2 = db.OpenRecordset("Select * from final")

reccount = rs2.RecordCount
Do While Not rs.EOF

Set rs2 = db.OpenRecordset("Select * from final where Old_Code like """ & rs("old_code") & "*""")

Do While Not rs2.EOF
rs2.Edit

If InStr(rs2("old_Code"), "^") <> 0 Or InStr(rs2("old_Code"), ":") <> 0 Then
rs2("New_Code") = rs("New_Code") & ".E"
ElseIf InStr(rs2("old_Code"), "-") <> 0 Then
rs2("New_Code") = rs("New_Code") & ".C"
Else
rs2("New_Code") = rs("New_Code")
End If

If InStr(rs2("old_Code"), " ") <> 0 Then
rs2("Description") = rs("Description") & " " & Right(rs2("old_code"), Len(rs2("old_code")) - InStr(1, rs2("old_code"), " "))
Else
rs2("Description") = rs("Description")
End If

rs2.Update
rs2.MoveNext
Loop

rs.MoveNext

Loop
i = i + 1

If i > reccount Then
i = reccount
End If

With Progressbar1
.Min = 0
.Max = reccount
.Value = i
.Refresh
End With
MsgBox "Finished."
End Sub