Reuse ADO recordsets

Often, you may want to reuse a recordset object in the same procedure. For instance, you may want to open a new SQL statement. If so, you may have wondered if it was necessary to destroy a recordset object before reusing it again, as in the following:

'process first RS
Set RS = New ADODB.Recordset
RS.Open SQL, Conn, ....
' do something with the RS
RS.Close
Set RS = Nothing

'process second RS
Set RS = New ADODB.Recordset
RS.Open SQL, Conn, ....
' do something with the RS
RS.Close
Set RS = Nothing

Fortunately, destroying the recordset object variable isn't necessary. ADO lets you reuse a recordset as much as you like, so long as you close it first. As a result, you could modify the above pseudo-code to look something like this:

Set RS = New ADODB.Recordset
'process first RS
RS.Open SQL, Conn, ....
' do something with the RS
RS.Close

'process second RS
RS.Open SQL, Conn, ....
' do something with the RS
RS.Close
Set RS = Nothing