To speed up iterative processes through large numbers of records by using DAO, declare object variables to refer to Field objects. The following example doesn’t use Field object variables to refer to Field objects. Instead, it refers to Field objects in the Fields collection. In this example, strDbPath
is the path to the NorthwindTables database:
Dim dbs As Database, rst As Recordset Set dbs = OpenDatabase(strDbPath) Set rst = dbs.OpenRecordset("Products") Do Until rst.EOF With rst .Edit .Fields!UnitPrice = rst!UnitPrice * 1.1 .Update .MoveNext End With Loop
This code performs better if you use Field object variables to refer to fields, as shown in the following example. In the following code, a Field object variable refers to the UnitPrice field in the recordset. When you return a reference to the field and assign it to a Field object variable, Visual Basic stores a reference to the field in the variable. The code runs more quickly because Visual Basic doesn’t have to return the reference each time the loop iterates. Again, the difference in performance is more noticeable for large recordsets than for small ones:
Dim dbs As Database, rst As Recordset Dim fld As Field Set dbs = OpenDatabase(strDbPath) Set rst = dbs.OpenRecordset("Products") Set fld = rst!UnitPrice Do Until rst.EOF With rst .Edit fld = fld * 1.1 .Update .MoveNext End With Loop
Note that this operation is even faster if you use an update query rather than updating a field in the recordset, record by record. The following example uses an update query to accomplish the same task, where strDbPath
is the path to the NorthwindTables database:
Dim dbs As Database, strSQL As String Set dbs = OpenDatabase(strDbPath) strSQL = "UPDATE Products SET Products.UnitPrice = UnitPrice *1.1;" dbs.Execute strSQL