Using Field Object Variables to Refer to Fields In Loops

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