ADODB.Recordset - can I do a .MoveLast on .BOF?

[Morris]

Hi!

Which one is a correct one?

If Not obJectTest.BOF Then
obJectTest.MoveLast
If obJectTest.RecordCount >= 2 Then
Blablabla
End If
End If
obJectTest.Close

Or:

obJectTest.MoveLast
If Not obJectTest.BOF Then
obJectTest.MoveLast
If obJectTest.RecordCount >= 2 Then
Blablabla
End If
End If
obJectTest.Close


or both?

 

[Stefan Hoffmann]

hi Morris,

Which one is a correct one?
If Not obJectTest.BOF Then
obJectTest.MoveLast
If obJectTest.RecordCount >= 2 Then
Blablabla
End If
End If
obJectTest.Close

obJectTest.MoveLast
If Not obJectTest.BOF Then
obJectTest.MoveLast
If obJectTest.RecordCount >= 2 Then
Blablabla
End If
End If
obJectTest.Close

If you are not needing the data:

obJectTest = CurrentDb.OpenRecordset(_
"SELECT Count(*) AS Cnt FROM Table").
If obJectTest![Cnt] > 1 Then ...

or

obJectTest.MoveLast
If obJectTest.RecorCount > 1 Then...

mfG
--> stefan <--

 

[pietlinden]

Hi!

Which one is a correct one?

If Not obJectTest.BOF Then
obJectTest.MoveLast
If obJectTest.RecordCount >= 2 Then
Blablabla
End If
End If
obJectTest.Close

Or:

obJectTest.MoveLast
If Not obJectTest.BOF Then
obJectTest.MoveLast
If obJectTest.RecordCount >= 2 Then
Blablabla
End If
End If
obJectTest.Close

Neither, I don't think.

If Not ObjectTest.EOF Then
objectTest.MoveLast
....
End If

 

[MikeB]

I think you've got those round the wrong way: you don't want to
MoveLast before you've tested RecordCount! If RecordCount is zero then
MoveLast will cause a run-time error.

With ADO, IIRC, RecordCount property of Recordset Object is not populated
accurately unless MoveLast is executed first.

 

[RoyVidar]

With ADO, IIRC, RecordCount property of Recordset Object is not
populated accurately unless MoveLast is executed first.

MikeB,
if you look it up in the help files, I think you'll find that what you
recall, describes the DAO .RecordCount, not ADO.

 

[Brendan Reynolds]

You want to be sure that the recordset has records before you attempt to
move to any record, be that the last record or any other. So ...

If Not (obJectTest.BOF And obJectTest.EOF) Then
obJectTest.MoveLast

Etc.

Your example testing only .BOF is probably OK in this scenario, but I'm in
the habit of testing for both .BOF and .EOF. The only circumstances under
which both conditions can be True is when the recordset is empty.

If you *only* want to know the number of records, and don't intend to access
any other data in the recordset, then you may find that using SELECT
Count(*) is faster, e.g. (untested air-code) ...

Dim lngRecords As Long
Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
With rst
Set .ActiveConnection = CurrentProject.Connection
.Open "SELECT Count(*) AS TheCount FROM YourTable
lngRecords = .Fields("TheCount")
.Close
End With

If lngRecords >= 2 Then
'etc
End If

 

[MikeB]

MikeB,
if you look it up in the help files, I think you'll find that what you
recall, describes the DAO .RecordCount, not ADO.

Well, more specifically (I had to take a peek as it has been awhile), with
(ADO) ForwardOnly cursor, the recordcount property is unknown until the cursor
is at .EOF or .MoveLast.

 

[RoyVidar]

Well, more specifically (I had to take a peek as it has been awhile),
with (ADO) ForwardOnly cursor, the recordcount property is unknown
until the cursor is at .EOF or .MoveLast.

Do you have any source for that DAO behaviour of ADO .Recordcount?

"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."

http://windowssdk.msdn.microsoft.com/en-us/library/ms676701.aspx

Besides, isn't the only way to get a ForwardOnly cursor with Jet,
to also use readonly locking (which means no .Move<thingie>)? I mean,
if you try opening a recordset with forwardonly and optimistic or
pessimistic, the cursor is coreced to keyset, isn't it, with either
optimistic or batch optimistic locking? Anyway, I've found that the
ADO cannot determine the number of records also in situations where
it should be able to do so (returns -1), and therefore use similar
construct as demonstrated by Brendan Reynolds elsethreads.

Here's a couple of other articles
http://forums.devx.com/showthread.php?t=45576&goto=nextoldest
http://www.devx.com/vb2themax/Tip/18489