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

[jbguernsey]

S. van Beek wrote:

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

[jbguernsey]

S. van Beek wrote:

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

If your recordset is rs (say) then

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

HTH
Jeff

[jbguernsey]

S. van Beek wrote:

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

If your recordset is rs (say) then

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

HTH
Jeff

[jbguernsey]

S. van Beek wrote:

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

If your recordset is rs (say) then

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

HTH
Jeff

[Tim Marshall]

S. van Beek wrote:

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

I do this two ways, one for when a form with an underlying
recordset/query/sql statement is opened and the other when I'm doing
operations on the recordset and the form is open.

The first, on _FIRST_ opening the form:

if forms!frmName.recordsetclone.eof then

'no records, do your stuff. If this is used in the on open event, set
cancel = true to avoid opeing the form.

I don't like using the recordsetclone for checking this sort of thing
when the form is open. Too much fiddling with having to make sure .move
first, etc.

I use a function like this (air code)

Function fCheckRecords() as Integer (or long if large numebrs of records
are involved)

dim strS as string
dim rst as dao.recordset
dim dbs as dao.database
'I personally don't use database variables in procedures anymore - I
use the David Fenton/Michka function see http://tinyurl.com/8nvl3

set dbs = Access.currentdb

strS = "Select count(*) as No from <enter rest of your tables and
criteria - idea is to have a query that returns one value)

set rst = dbs.openrecordset(strs, dbopensnapshot)

fCheckRecords = rst.fields!No

rst.close

dbs.close

set rst = ntohing

set dbs = nothing

end function

This function is used like:

If fcheckRecords = 0 then <do stuff for empty recordset>

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Gord]

Immediately after opening the DAO Recordset, all I've ever used is

If rs.BOF And rs.EOF Then
' the recordset is empty
End If

[lylefair]

Immediately upon opening

EOF <=> no records
BOF <=> no records

(if paid by word)
BOF AND EOF <=> no records

[David W. Fenton]

jb********@aol.com wrote in
news:11**********************@g43g2000cwa.googlegr oups.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

[David W. Fenton]

Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote in
news:dg**********@coranto.ucs.mun.ca:

S. van Beek wrote:

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

I do this two ways, one for when a form with an underlying
recordset/query/sql statement is opened and the other when I'm
doing operations on the recordset and the form is open.

The first, on _FIRST_ opening the form:

if forms!frmName.recordsetclone.eof then

'no records, do your stuff. If this is used in the on open
event, set
cancel = true to avoid opeing the form.

I don't like using the recordsetclone for checking this sort of
thing when the form is open. Too much fiddling with having to
make sure .move first, etc.

Why not just check Me.Recordsetclone.RecordCount? So far as I know,
that's always going to be accurate in the same way as .RecordCount
for any DAO recordset, no?

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

[Randy Harris]

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn*********************************@216.196.9 7.142...

jb********@aol.com wrote in
news:11**********************@g43g2000cwa.googlegr oups.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 (adApproxPosition) 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 (adApproxPosition) 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.googlegr oups.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 (adApproxPosition) 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 (adApproxPosition) 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********@bway.net.invalid> wrote in
news:Xn**********************************@216.196. 97.142:

"lylefair" <ly******@yahoo.ca> wrote in
news:11**********************@g14g2000cwa.googlegr oups.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 (adApproxPosition) 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
(adApproxPosition) 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******@sympatico.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********@bway.net.invalid> wrote in
news:Xn**********************************@216.196. 97.142:

Quintal <rq******@sympatico.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******@sympatico.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.

[Randy Harris]

"Trevor Best" <no****@localhost.invalid> wrote in message
news:43***********************@news.zen.co.uk...

David W. Fenton wrote:

Quintal <rq******@sympatico.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.

With ADO, rs.EOF will never be false if there are 0 records in the
RecordSet. It won't permit a MoveAnywhere and a Find won't change rs.EOF.
As I mentioned earlier, the .RecordCount works only with a KeySet cursor
(don't know if that's what you meant by broken)

Personally, I almost never use rs.BOF. I can't think of any reason to check
it unless you use MovePrevious.

[lylefair]

Yes, I find no reason to use BOF.

But RecordCount is returned from Static as well as KeySet Cursor
types.

Complicating this, at least here in Canader, is that no matter to what
value we set the CursorType, when we open a recordset using
CurrentProject.Connection or CurrentProject.AccessConnection it is
always opened as adOpenStatic (3), thus always allowing for RecordCount
to be returned.

Perhaps others can confirm this, so that we will not attribute it to
some error here.

(with something like this air code):

Sub test()
Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Set c = New ADODB.Connection
c.Open CurrentProject.BaseConnectionString & ";USER
ID=USERID;PASSWORD=PASSWORD"
Set r = New ADODB.Recordset
With r
..CursorType = adOpenForwardOnly '0
..Open "SELECT * FROM FFDBATransactions", CurrentProject.Connection
MsgBox .CursorType
MsgBox .RecordCount
..Close

..CursorType = adOpenForwardOnly '0
..Open "SELECT * FROM FFDBATransactions", c
MsgBox .CursorType
MsgBox .RecordCount
..Close
End With
End Sub

[lylefair]

It's these seemingly endless number of anomalies that has made me give
up on Access as a front end for MS-SQL.

[Randy Harris]

"lylefair" <ly******@yahoo.ca> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

Yes, I find no reason to use BOF.

But RecordCount is returned from Static as well as KeySet Cursor
types.

Complicating this, at least here in Canader, is that no matter to what
value we set the CursorType, when we open a recordset using
CurrentProject.Connection or CurrentProject.AccessConnection it is
always opened as adOpenStatic (3), thus always allowing for RecordCount
to be returned.

Perhaps others can confirm this, so that we will not attribute it to
some error here.

(with something like this air code):

Sub test()
Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Set c = New ADODB.Connection
c.Open CurrentProject.BaseConnectionString & ";USER
ID=USERID;PASSWORD=PASSWORD"
Set r = New ADODB.Recordset
With r
.CursorType = adOpenForwardOnly '0
.Open "SELECT * FROM FFDBATransactions", CurrentProject.Connection
MsgBox .CursorType
MsgBox .RecordCount
.Close

.CursorType = adOpenForwardOnly '0
.Open "SELECT * FROM FFDBATransactions", c
MsgBox .CursorType
MsgBox .RecordCount
.Close
End With
End Sub

Strange indeed. You're correct, of course, about adOpenStatic also providing
the RecordCount.

I always use this sort of syntax, rather than specifying the CursorType
separately (not for any good reason, it's the way I got used to doing it):

rs.Open SQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly

It seems to always use the cursor type I specify. adOpenForwardOnly if I
just leave the cursor type off.

[Randy Harris]

"lylefair" <ly******@yahoo.ca> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

It's these seemingly endless number of anomalies that has made me give
up on Access as a front end for MS-SQL.

Can I ask, what you find preferable for the front end?

[lylefair]

What do you get when running something like this?
Dim rs As ADODB.Recordset
Dim SQL As String
SQL = "SELECT * FROM FFDBATransactions WHERE TotalAmount>100"
Set rs = New ADODB.Recordset
rs.Open SQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly

Debug.Print "Cursor Type Is Keyset? ", rs.CursorType = adOpenKeyset
I get False

Debug.Print "Cursor Type Is Static? ", rs.CursorType = adOpenStatic
I get True

[lylefair]

ASP and HTA.

[Randy Harris]

"lylefair" <ly******@yahoo.ca> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

What do you get when running something like this?
Dim rs As ADODB.Recordset
Dim SQL As String
SQL = "SELECT * FROM FFDBATransactions WHERE TotalAmount>100"
Set rs = New ADODB.Recordset
rs.Open SQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly

Debug.Print "Cursor Type Is Keyset? ", rs.CursorType = adOpenKeyset
I get False

Debug.Print "Cursor Type Is Static? ", rs.CursorType = adOpenStatic
I get True

Very strange indeed. I'm using Access 2000, in case that makes a
difference.

---------------------------------------------------
Dim rs As ADODB.Recordset
Dim SQL As String
SQL = "SELECT * FROM Itineraries"
Set rs = New ADODB.Recordset
rs.Open SQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
Debug.Print "Cursor Type Is Keyset? ", rs.CursorType = adOpenKeyset
Debug.Print "Cursor Type Is Static? ", rs.CursorType = adOpenStatic
rs.Close
---------------------------------------------------
Cursor Type Is Keyset? True
Cursor Type Is Static? False

---------------------------------------------------

I tried it the other way, out of curiosity.

---------------------------------------------------
Dim rs As ADODB.Recordset
Dim SQL As String
SQL = "SELECT * FROM Itineraries"
Set rs = New ADODB.Recordset
rs.Open SQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
Debug.Print "Cursor Type Is Keyset? ", rs.CursorType = adOpenKeyset
Debug.Print "Cursor Type Is Static? ", rs.CursorType = adOpenStatic
rs.Close
---------------------------------------------------
Cursor Type Is Keyset? False
Cursor Type Is Static? True
---------------------------------------------------

I also tried it without specifying cursor type. They both showed false.

[lylefair]

Hmmmmm.
I tried this on Access 2002 and Access 2003 with local and remote adps
and local mdbs on two different machines 50 km apart with various
manifestations of the ADO library from 2.1 to 2.8.
I always go a Static cursor type.
Perhaps, I am missing something; I will check and recheck and post
anything I find.

[David W. Fenton]

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

Yes, I find no reason to use BOF.

It would be helpful if those posting on this subject would clearly
indicate whether they are talking about DAO or ADO recordsets.

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

[David W. Fenton]

"lylefair" <ly******@yahoo.ca> wrote in
news:11*********************@g44g2000cwa.googlegro ups.com:

Hmmmmm.
I tried this on Access 2002 and Access 2003 with local and remote
adps and local mdbs on two different machines 50 km apart with
various manifestations of the ADO library from 2.1 to 2.8.
I always go a Static cursor type.
Perhaps, I am missing something; I will check and recheck and post
anything I find.

Does it make a difference what your back end is, and MDB or SQL
Server?

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