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]

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

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.

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. AccessConnectio n 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.Connectio n
Dim r As ADODB.Recordset
Set c = New ADODB.Connectio n
c.Open CurrentProject. BaseConnectionS tring & ";USER
ID=USERID;PASSW ORD=PASSWORD"
Set r = New ADODB.Recordset
With r
..CursorType = adOpenForwardOn ly '0
..Open "SELECT * FROM FFDBATransactio ns", CurrentProject. Connection
MsgBox .CursorType
MsgBox .RecordCount
..Close

..CursorType = adOpenForwardOn ly '0
..Open "SELECT * FROM FFDBATransactio ns", 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******** *************@g 14g2000cwa.goog legroups.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. AccessConnectio n 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.Connectio n
Dim r As ADODB.Recordset
Set c = New ADODB.Connectio n
c.Open CurrentProject. BaseConnectionS tring & ";USER
ID=USERID;PASSW ORD=PASSWORD"
Set r = New ADODB.Recordset
With r
.CursorType = adOpenForwardOn ly '0
.Open "SELECT * FROM FFDBATransactio ns", CurrentProject. Connection
MsgBox .CursorType
MsgBox .RecordCount
.Close

.CursorType = adOpenForwardOn ly '0
.Open "SELECT * FROM FFDBATransactio ns", 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. adOpenForwardOn ly if I
just leave the cursor type off.

[Randy Harris]

"lylefair" <ly******@yahoo .ca> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.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 FFDBATransactio ns 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.goo glegroups.com.. .

What do you get when running something like this?
Dim rs As ADODB.Recordset
Dim SQL As String
SQL = "SELECT * FROM FFDBATransactio ns 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******** *************@g 14g2000cwa.goog legroups.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