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