PercentPosition Property Example

This example selects and changes records in the Orders recordset in the Nwindex.mdb database, and then it copies the recordset to Sheet1. The status bar shows the percentage of records that have been changed.

To create the Nwindex.mdb database, run the Microsoft Excel example for the CreateDatabase method.

Dim db As Database, rs As Recordset, sQLText As String
Set db = Workspaces(0).OpenDatabase(Application _
    .DefaultFilePath & "\NWINDEX.MDB")
sQLText = "SELECT [ORDER_ID], [ORDER_AMT] FROM Orders;"
Set rs = db.OpenRecordset(sQLText, dbOpenDynaset)
rs.MoveFirst
Sheets("Sheet1").Activate
Do While Not rs.EOF
    rs.Edit
    cur = rs.Fields("ORDER_AMT").Value
    rs.Fields("ORDER_AMT").Value = cur + 8.33
    rs.Update
    Application.StatusBar = rs.PercentPosition & "% of the" _
        & " records have been replaced."
    rs.MoveNext
Loop
Application.StatusBar = "Done"
rs.MoveFirst
numberOfRows = ActiveCell.CopyFromRecordset(rs)
MsgBox numberOfRows & " Records have been changed"
rs.Close
db.Close