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

You might also like...

Comments

ElementK Journals

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away.” - Antoine de Saint Exupéry