Clearing a Recordset for reuse

[+Bob+]

I'd like to use an RS to get a total count of available records. I
would then like to reuse the RS object to get the specific record I
need (instead of creating a new RS).

What command should I use to just "clear" the RS? RS.close? Set RS=
nothing? Or is there a better way?

Thanks,

 

[Daniel Crichton]

+Bob+ wrote on Sun, 05 Apr 2009 17:32:15 -0400:

I'd like to use an RS to get a total count of available records. I
would then like to reuse the RS object to get the specific record I
need (instead of creating a new RS).

What command should I use to just "clear" the RS? RS.close? Set RS=
nothing? Or is there a better way?

Thanks,

Just use RS.close to close it. If you set it to Nothing then you will need
to set it again to a new object reference to use it a second time which is
wasteful on resources, although the effect may well be almost negligible
depending on your hardware and software configurations.

 

[Bob Barrows]

I'd like to use an RS to get a total count of available records. I
would then like to reuse the RS object to get the specific record I
need (instead of creating a new RS).

What command should I use to just "clear" the RS? RS.close? Set RS=
nothing? Or is there a better way?

As Saniel says, RS.Close would be appropriate.
However, since it sounds as if you know which record you want to retrieve
without knowing how many are available, you might consider retrieving a
single recordset that contains both pieces of information, by using a
subquery:

select field1,...,fieldN,
(select count(*) from table where ...) as totalavailable
from table where ...

Alternatively, if your backend database supports batched commands (Jet
doesnt, SQL Server does), you can issue two sql statements that each return
records, and use NextRecordset to retrieve each resultset.

Personally, I would prefer the first approach: if the information I need can
be retrieved in a single resultset, then it really makes sense to do it that
way, avoiding unnecessary round trips to the database.

 

[+Bob+]

As Saniel says, RS.Close would be appropriate.
However, since it sounds as if you know which record you want to retrieve
without knowing how many are available, you might consider retrieving a
single recordset that contains both pieces of information, by using a
subquery:

select field1,...,fieldN,
(select count(*) from table where ...) as totalavailable
from table where ...

Alternatively, if your backend database supports batched commands (Jet
doesnt, SQL Server does), you can issue two sql statements that each return
records, and use NextRecordset to retrieve each resultset.

Personally, I would prefer the first approach: if the information I need can
be retrieved in a single resultset, then it really makes sense to do it that
way, avoiding unnecessary round trips to the database.

Thanks, that will do it.