Check on empty record set in VBA

[S. van Beek]

Dear reader

A record set can be empty because the condition in the query delivers no
records.

Is there a VBA code to check the status of a record set, record set empty
(no records) or with records?

Thanks for any help.

Simon van Beek

[Randy Harris]

"David W. Fenton" <dX********@bwa y.net.invalid> wrote in message
news:Xn******** *************** **********@216. 196.97.142...

jb********@aol. com wrote in
news:11******** **************@ g43g2000cwa.goo glegroups.com:

S. van Beek wrote:

A record set can be empty because the condition in the query
delivers no records.

Is there a VBA code to check the status of a record set, record
set empty (no records) or with records?

If your recordset is rs (say) then

rs.movelast
if rs.recordcount = 0 then
'no rows
else
'rows returned
end if

The original poster does not specify DAO or ADO.

In DAO, you don't need to .MoveLast to know if the recordset is
empty. You only need to .MoveLast when you want an accurate count of
the records returned. If there are records returned, the recordcount
will always be 1 or more, and when none are returned, it will always
be zero, no matter whether you .MoveLast or not.

ADO is different. It returns different things. I don't use ADO, so I
can't tell you what it returns, but the help file for the ADO
recordset object ought to give you the information you need.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

With ADO the RecordCount property is only available with a KeySet cursor.
There is no need to MoveLast to get the count.

Regardless of cursor type, if there are no records in the recordset, EOF
will be true immediately after opening the recordset.

rs.Open ("Select.... ")
If rs.EOF = True Then
' There were no records returned
End If

[lylefair]

ADO 2.8 Documentation:

RecordCount Property
Indicates the number of records in a Recordset object.

Return Value
Returns a Long value that indicates the number of records in the
Recordset.

Remarks
Use the RecordCount property to find out how many records are in a
Recordset object. The property returns -1 when ADO cannot determine the
number of records or if the provider or cursor type does not support
RecordCount. Reading the RecordCount property on a closed Recordset
causes an error.

If the Recordset object supports approximate positioning or
bookmarks-that is, Supports (adApproxPositi on) or Supports
(adBookmark), respectively, return True-this value will be the exact
number of records in the Recordset, regardless of whether it has been
fully populated. If the Recordset object does not support approximate
positioning, this property may be a significant drain on resources
because all records will have to be retrieved and counted to return an
accurate RecordCount value.

NOTE: In ADO versions 2.8 and earlier, the SQLOLEDB provider fetches
all records when a server-side cursor is used despite the fact that it
returns True for both Supports (adApproxPositi on) and Supports
(adBookmark),

The cursor type of the Recordset object affects whether the number of
records can be determined. The RecordCount property will return -1 for
a forward-only cursor; the actual count for a static or keyset cursor;
and either -1 or the actual count for a dynamic cursor, depending on
the data source.

[David W. Fenton]

"lylefair" <ly******@yahoo .ca> wrote in
news:11******** **************@ g14g2000cwa.goo glegroups.com:

ADO 2.8 Documentation:

RecordCount Property
Indicates the number of records in a Recordset object.

Return Value
Returns a Long value that indicates the number of records in the
Recordset.

Remarks
Use the RecordCount property to find out how many records are in a
Recordset object. The property returns -1 when ADO cannot
determine the number of records or if the provider or cursor type
does not support RecordCount. Reading the RecordCount property on
a closed Recordset causes an error.

If the Recordset object supports approximate positioning or
bookmarks-that is, Supports (adApproxPositi on) or Supports
(adBookmark), respectively, return True-this value will be the
exact number of records in the Recordset, regardless of whether it
has been fully populated. If the Recordset object does not support
approximate positioning, this property may be a significant drain
on resources because all records will have to be retrieved and
counted to return an accurate RecordCount value.

NOTE: In ADO versions 2.8 and earlier, the SQLOLEDB provider
fetches all records when a server-side cursor is used despite the
fact that it returns True for both Supports (adApproxPositi on) and
Supports (adBookmark),

The cursor type of the Recordset object affects whether the number
of records can be determined. The RecordCount property will return
-1 for a forward-only cursor; the actual count for a static or
keyset cursor; and either -1 or the actual count for a dynamic
cursor, depending on the data source.

That doesn't seem to indicate whether it indicates 0 for an empty
recordset.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Bob Quintal]

"David W. Fenton" <dX********@bwa y.net.invalid> wrote in
news:Xn******** *************** ***********@216 .196.97.142:

"lylefair" <ly******@yahoo .ca> wrote in
news:11******** **************@ g14g2000cwa.goo glegroups.com:

ADO 2.8 Documentation:

RecordCount Property
Indicates the number of records in a Recordset object.

Return Value
Returns a Long value that indicates the number of records in
the Recordset.

Remarks
Use the RecordCount property to find out how many records are
in a Recordset object. The property returns -1 when ADO
cannot determine the number of records or if the provider or
cursor type does not support RecordCount. Reading the
RecordCount property on a closed Recordset causes an error.

If the Recordset object supports approximate positioning or
bookmarks-that is, Supports (adApproxPositi on) or Supports
(adBookmark), respectively, return True-this value will be
the exact number of records in the Recordset, regardless of
whether it has been fully populated. If the Recordset object
does not support approximate positioning, this property may
be a significant drain on resources because all records will
have to be retrieved and counted to return an accurate
RecordCount value.

NOTE: In ADO versions 2.8 and earlier, the SQLOLEDB
provider fetches all records when a server-side cursor is
used despite the fact that it returns True for both Supports
(adApproxPositi on) and Supports (adBookmark),

The cursor type of the Recordset object affects whether the
number of records can be determined. The RecordCount property
will return -1 for a forward-only cursor; the actual count
for a static or keyset cursor; and either -1 or the actual
count for a dynamic cursor, depending on the data source.

That doesn't seem to indicate whether it indicates 0 for an
empty recordset.

In my experience, and as claimed by Randy Harris earlier in this
thread, testing for .EOF always returns the correct answer upon
opening a recordset.

--
Bob Quintal

PA is y I've altered my email address.

[David W. Fenton]

Quintal <rq******@sympa tico.ca> wrote in
news:Xn******** **************@ 207.35.177.135:

In my experience, and as claimed by Randy Harris earlier in this
thread, testing for .EOF always returns the correct answer upon
opening a recordset.

I'm foggy on this, but I seem to remember that you must check both
EOF and BOF to be sure, and that there are some unique circumstances
where it can be an incorrect answer (though I don't remember if it
was a false positive or false negative).

I Googled a bit on this and couldn't find anything, so perhaps it's
just some cobwebs in my brain, but it was my reason for sticking
with checking .RecordCount instead.

Another reason I check .RecordCount is that I'd rather check a
single property rather than two, not that I think the performance
difference is enough to matter, but because it seems more elegant.

Of course, I never use ADO, so I have the advantag of using DAO's
more reliableBob .RecordCount property.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Bob Quintal]

"David W. Fenton" <dX********@bwa y.net.invalid> wrote in
news:Xn******** *************** ***********@216 .196.97.142:

Quintal <rq******@sympa tico.ca> wrote in
news:Xn******** **************@ 207.35.177.135:

In my experience, and as claimed by Randy Harris earlier in
this thread, testing for .EOF always returns the correct
answer upon opening a recordset.
I'm foggy on this, but I seem to remember that you must check
both EOF and BOF to be sure, and that there are some unique
circumstances where it can be an incorrect answer (though I
don't remember if it was a false positive or false negative).

The true test of an empty recordset is RS.BOF AND RS.EOF.
However, Immediately after opening, .BOF is *always* asserted,
so just testing for EOF is sufficient.
I Googled a bit on this and couldn't find anything, so perhaps
it's just some cobwebs in my brain, but it was my reason for
sticking with checking .RecordCount instead.

Another reason I check .RecordCount is that I'd rather check a
single property rather than two, not that I think the
performance difference is enough to matter, but because it
seems more elegant.
Your elegance and mine are not the same: even testing for both
properties is prettier in my mind than

If .RecordCount = 0 Then

IF .BOF and .EOF then

IF .EOF Then
Of course, I never use ADO, so I have the advantag of using
DAO's more reliableBob .RecordCount property.

--
Bob Quintal

PA is y I've altered my email address.

[lylefair]

Bob Quintal wrote:

The true test of an empty recordset is RS.BOF AND RS.EOF.
However, Immediately after opening, .BOF is *always* asserted,
so just testing for EOF is sufficient.

No!
Try this using the name of a table that has records.

Sub temp()
Dim r As DAO.Recordset
Set r = DBEngine(0)(0). OpenRecordset(" Table1")
MsgBox r.BOF 'False
Set r = Nothing
End Sub

[Trevor Best]

David W. Fenton wrote:

Quintal <rq******@sympa tico.ca> wrote in
news:Xn******** **************@ 207.35.177.135:

In my experience, and as claimed by Randy Harris earlier in this
thread, testing for .EOF always returns the correct answer upon
opening a recordset.

I'm foggy on this, but I seem to remember that you must check both
EOF and BOF to be sure, and that there are some unique circumstances
where it can be an incorrect answer (though I don't remember if it
was a false positive or false negative).

I Googled a bit on this and couldn't find anything, so perhaps it's
just some cobwebs in my brain, but it was my reason for sticking
with checking .RecordCount instead.

Aye, I've ran into this phenom a couple of times, both .eof and .bof
were false yet there were no records, where on Earth it thought the
cursor was I don't know. .RecordCount is more reliable in DAO and broken
in ADO.

[Darryl Kerkeslager]

"lylefair" <ly******@yahoo .ca> wrote

Bob Quintal wrote:

The true test of an empty recordset is RS.BOF AND RS.EOF.
However, Immediately after opening, .BOF is *always* asserted,
so just testing for EOF is sufficient.

No!
Try this using the name of a table that has records.

Sub temp()
Dim r As DAO.Recordset
Set r = DBEngine(0)(0). OpenRecordset(" Table1")
MsgBox r.BOF 'False
Set r = Nothing
End Sub

This doesn't show that using .EOF is insufficient, though.

Certainly, (If rs.EOF) will not accurately indicate an empty recordset if
you do something to move the cursor - but the point is, that at *opening* of
a recordset, (If rs.EOF) will accurately indicate if there are no records.
You should only need to test (If rs.BOF And rs.EOF) if you've moved the
cursor.

.... Right?
--
Darryl Kerkeslager

[lylefair]

Yes.