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 something 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:
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 such a 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?