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.

“To iterate is human, to recurse divine” - L. Peter Deutsch