Looping through large recordsets using ADODB.Field pointers

If your recordset has a lot of data - hundreds or thousands of rows, use a pointer to the column to improve performance. It is much faster than doing this:

rsData("column_a").Value

Here is an example:

'Declare
Dim objConn As ADODB.Connection
Dim rsData As ADODB.Recordset
Dim objFieldX As ADODB.Field
Dim objFieldY As ADODB.Field
Dim strCmd As String

'Initialize
Set objConn = New ADODB.Connection

'Open connection
mobjDBConn.Open "....my connection string..."

'Fetch data
Set rsData = mobjConn.Execute("...my sql...")


'Get a pointer to the fields
Set objFieldX = rsData("column_x")
Set objFieldY = rsData("column_y")

If rsData.BOF And rsData.EOF Then
'Do something if recordset is empty
Else
Do Until rsData.EOF
'Do something with the data
'a = objFieldX.Value
'b = objFieldY.Value

rsData.MoveNext
Loop
End If

'Close connection
If objConn.State <> adStateClosed Then
objConn.Close
End If

'Clean up
Set rsData = Nothing
Set objConn = Nothing
Set objFieldX = Nothing
Set objFieldY = Nothing