Module works fine in 97 but not in 2000; I'm Desperate!

[dowally]

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

 

[rbowes]

Have you set a referene to the DAO 3.6 library? When you do, you'll have to put a DAO reference in front of your DAO objects like DAO.Database (Database is not an object in ADO, which is the default library in Access 2000). Make sure that you put DAO.Recordset because both DAO and ADO have recordset objects.

That may solve your problem.

Let me know.

Bob

 

[rbowes]

Oh, sorry I forgot to mention that the database bloat has been a big issue for alot of Access 2000 developers. But, unlike Access 97, you can set the mdb to "compact on close"
Under Tools|Options|General. That way you never have to do it manually.

Bob

 

[dowally]

Bob:

Thanks for the advice. Unfortunately it doesn't seem to be the answer. After making the changes you suggested, the code ran a bit faster (45 secs). The file size still grew the same amount. Compacting on close doesn't help in my situation. My code example is only a small portion of code that I'm using in 97. The entire code goes thru many steps such as importing text files, adding/changing fields in tables, running queries, etc. I would need to compact the database numerous times in the midst of running the code. In 97, the whole process takes 60 seconds and I was hoping to be able to do the same thing in 2000.

Paul