Caching References to Objects and Collections

When you refer to an object or a collection more than once, you can improve performance by caching a reference to that object or collection. There are three ways to do this:

By returning a reference to an object or collection and assigning it to an object variable
By using the For Each…Next statement when you refer to each object in a collection
By using the With statement when you refer to an object and its properties, methods, or collections repeatedly

When you refer to an object or collection more than once in your code, you may want to create an object variable and assign a reference to the object to that object variable. Not only does this make your code faster for lengthy operations, it also makes it easier to read.

The following two examples both return the names of all tables in a database, all fields in each table, and the names and values of all properties on those fields. The first example, which stores references to objects in object variables, is considerably faster than the second. In these examples, strDbPath is the path to a database:

' 1 - Faster, easier to read.
Dim dbs As Database, tdf As TableDef
Dim fld As Field, prp As Property
	
Set dbs = OpenDatabase(strDbPath)
For Each tdf In dbs.TableDefs
	Debug.Print tdf.Name
	For Each fld In tdf.Fields
		Debug.Print fld.Name
		For Each prp In fld.Properties
			On Error Resume Next
			Debug.Print prp.Name, prp.Value
		Next prp
	Next fld
Next tdf
dbs.Close
Set dbs = Nothing

' 2 - Slower, more cumbersome.
Dim intW As Integer, intX As Integer, intY As Integer
	
OpenDatabase strDbPath
For intW = 0 To DBEngine(0)(0).TableDefs.Count - 1
	Debug.Print DBEngine(0)(0).TableDefs(intW).Name
	For intX = 0 To DBEngine(0)(0).TableDefs(intW).Fields.Count - 1
		Debug.Print DBEngine(0)(0).TableDefs(intW).Fields(intX).Name
		For intY = 0 To _
			DBEngine(0)(0).TableDefs(intW).Fields(intX).Properties.Count - 1
			On Error Resume Next
			Debug.Print _
				DBEngine(0)(0).TableDefs(intW).Fields(intX).Properties(intY).Name, _
				DBEngine(0)(0).TableDefs(intW).Fields(intX).Properties(intY).Value
		Next
	Next
Next

You can improve performance by using the For Each…Next and With…End With statements. Both of these cache a reference to an object, so that your code doesn’t have to repeatedly retrieve the reference in order to use the object.

The following example uses both of these statements to print all of the values in a recordset. In this example, strDbPath is the path to a database, and strRstSource is the source for a recordset:

Dim dbs As Database, tdf As TableDef
Dim rst As Recordset, fld As Field

Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset(strRstSource)

With rst
	Do Until .EOF
		For Each fld In rst.Fields
			Debug.Print fld.Name, fld.Value
		Next fld
		.MoveNext
	Loop
	Debug.Print
End With
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

The following example uses a For loop rather than the For Each…Next statement, and it refers to the Recordset object variable explicitly each time it’s used rather than caching it with the With statement. The performance differences are not apparent for small recordsets, but become more significant with larger recordsets. When strDbPath is the path to the NorthwindTables database and strRstSource is the Orders table, the following example runs several seconds more slowly than the preceding one:

Dim dbs As Database, tdf As TableDef
Dim rst As Recordset, fld As Field
Dim intC As Integer

Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset(strRstSource)

Do Until rst.EOF
	For intC = 0 To rst.Fields.Count - 1
		Debug.Print rst.Fields(intC).Name, rst.Fields(intC).Value
	Next
	rst.MoveNext
Loop
Debug.Print
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

Another important consideration for performance is whether or not your object variables are strictly typed. When you strictly type an object variable, you declare the object variable according to the type of object that it will represent. In the following example, the object variables are not strictly typed according to the object type they are to represent. When a variable isn’t typed, Visual Basic automatically designates it as type Variant. This example is otherwise identical to the preceding example, but runs at nearly half the speed for the Orders table in the NorthwindTables database:

Dim dbs, tdf
Dim rst, fld
Dim intC

Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset(strRstSource)

Do Until rst.EOF
	For intC = 0 To rst.Fields.Count - 1
		Debug.Print rst.Fields(intC).Name, _
			rst.Fields(intC).Value
	Next
	rst.MoveNext
Loop
Debug.Print
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing