Versions: 95 +
Looping through a DAO recordset is a pretty common requirement, isn't it? And in each loop, more often than not you'll want to do somthing to the value or values that you find in certain fields. Ever noticed how such an iterative process is not particularly fast? Especially if you have a big recordset...
Here's an example of a traditional iterative loop - each time through, the code assigns the name of a supplier to a string variable (in other words, this is about the simplest example I could think of):
.
.
.
Dim strSupplier As String
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblSupplier", dbOpenDynaset)
rst.MoveFirst
Do Until rst.EOF
strSupplier = rst("SupplierName")
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
.
.
.
Pretty standard stuff, I'm sure you'll agree, and maybe even how you'd write the code yourself if asked. Bill Gates would undoubtedly approve. But it's slow! If your supplier table is complex and holds several thousand records this is going to be very tedious! And all we're doing is assigning a value to a string variable. What if we want to do something more complex? And maybe to/with more than one field value? We need a faster method... fortunately, help is at hand in the shape of the Field object. Compare the example above to the code that follows, which has exactly the same effect but can be 20+ times faster:
.
.
.
'Code sample from Accessory http://www22.brinkster.com/accessory
Dim strSupplier As String
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblSupplier", dbOpenDynaset)
Set fld = rst("SupplierName")
rst.MoveFirst
Do Until rst.EOF
strSupplier = fld
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
.
.
.
Neat, isn't it? Whether by accident or design, once the Field object variable has been defined as equating to a certain field in a Recordset object, it automatically updates itself for each record as you iterate through that recordset. And anything that can give you up to a 2000% hike in speed (for simple DAO recordset operations - differences for ADO recordsets are less apparent) in an Access environment has got to be worth exploring, surely?
