VBA SQL Select stmt multiple rows found but not getting RecordCount > 1

[don.fleming]

Hi folks: Am doing a VBA SQL Select stmt with multiple rows found and
not getting RecordCount > 1. I've verified there are multiple rows
found by copying my SQL stmt from Debug window and pasting it to a
query - retrieves many rows. I need to program to handle many Rows in
VBA. Suggestions much appreciated!

Here is an excerpt of my VBA code after the SQLstmt is built:

Debug.Print SQLstmt
Set RecSet1 = SQLtbl.OpenRecordset(SQLstmt)
If (RecSet1.RecordCount > 1) Then ...

*NOTE: I posted this qstn yesterday and cannot find it.. Not sure
why, will try posting one more time :~|

- Don Fleming, Bellevue Wa.

[jimfortune]

do*********@pse.com wrote:

Hi folks: Am doing a VBA SQL Select stmt with multiple rows found and
not getting RecordCount > 1. I've verified there are multiple rows
found by copying my SQL stmt from Debug window and pasting it to a
query - retrieves many rows. I need to program to handle many Rows in
VBA. Suggestions much appreciated!

Here is an excerpt of my VBA code after the SQLstmt is built:

Debug.Print SQLstmt
Set RecSet1 = SQLtbl.OpenRecordset(SQLstmt)
If (RecSet1.RecordCount > 1) Then ...

*NOTE: I posted this qstn yesterday and cannot find it.. Not sure
why, will try posting one more time :~|

- Don Fleming, Bellevue Wa.

After Set RecSet1...
RecSet1.MoveLast

This is a known and common problem.

James A. Fortune

[Tim Marshall]

do*********@pse.com wrote:

Debug.Print SQLstmt
Set RecSet1 = SQLtbl.OpenRecordset(SQLstmt)
If (RecSet1.RecordCount > 1) Then ...

Try the following to see if it helps:

Debug.Print SQLstmt
Set RecSet1 = SQLtbl.OpenRecordset(SQLstmt)
'Go to the end of the recordset and, if necessary, go back to the
'start. This will sewt recordcount to the number of records
RecSet.MoveLast
RecSet.MoveFirst
If (RecSet1.RecordCount > 1) Then ...

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

[don.fleming]

Yes, that does it allright! Big Thanks Tim!

I'll add it is too bad these stmts needed though, shouldn't have to,
but now that I know I'm off & running :~)

[David W. Fenton]

do*********@pse.com wrote in
news:11*********************@f14g2000cwb.googlegro ups.com:

Yes, that does it allright! Big Thanks Tim!

I'll add it is too bad these stmts needed though, shouldn't have
to, but now that I know I'm off & running :~)

The reason this happens is that Jet does not load the entire
recordset immediately, but only requests data pages as they are
needed. It cannot calculate an accurate recordcount until all the
data pages of the table have been retrieved. The MoveLast forces
that to happen.

However, don't think that .Recordcount is not accurate in some
respects. If it's anything other than 0 then you know that some
records have been returned, and if it's 0, you definitely know that
no records matched your criteria. If it's not 0, it may or may not
be accurate, something you can't know until you force it to update
with the .MoveLast.

In most cases, you don't need the recordcount, all you need to check
is if the .Recordcount is not 0.

If you are opening your recordset just to get a recordcount, then
it's more efficient to use Count(*) as your SQL (instead of
retrieving all the matching records and then doing a .MoveLast),
because Jet SQL is optimized to be more efficient with that than it
is with the .MoveLast.

The only case where using .MoveLast to get the accurate record count
is justified is if you need to retrieve the accurate recordcount at
the same time as you're doing something else in your recordset. In
my e, this happens a lot less often than you'd think it would.

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