When I run the code below in Access 97, it loops thru 16,000+ records and takes about 4 seconds. The mdb file size changes from 1788KB to 1872KB. When I run the exact same code in Access 2000, it takes 60 seconds and the file size balloons from 2364KB to 69,932KB. After compacting, file size returns to normal. Any and all comments will be appreciated. Thanks!
Sub newRemarks()
'take the box number from the remarks field and place it in a new field
'called newremarks
Dim dbs As Database
Dim rst As Recordset
Dim strBox As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("certmike", dbOpenTable)
Do While rst.EOF = False 'code will loop until it reaches the end of the recordset
If IsNull(rst!Remarks) Then
rst.MoveNext
Else
If Mid(rst!Remarks, 1, 1) = "m" Then
strBox = Mid(rst!Remarks, 3, 3)
With rst
.Edit
!newRemarks = strBox
.Update
.MoveNext
End With
Else
rst.MoveNext
End If
End If
Loop 'loop back to do to go thru all records in table
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub
Sub newRemarks()
'take the box number from the remarks field and place it in a new field
'called newremarks
Dim dbs As Database
Dim rst As Recordset
Dim strBox As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("certmike", dbOpenTable)
Do While rst.EOF = False 'code will loop until it reaches the end of the recordset
If IsNull(rst!Remarks) Then
rst.MoveNext
Else
If Mid(rst!Remarks, 1, 1) = "m" Then
strBox = Mid(rst!Remarks, 3, 3)
With rst
.Edit
!newRemarks = strBox
.Update
.MoveNext
End With
Else
rst.MoveNext
End If
End If
Loop 'loop back to do to go thru all records in table
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub