Recordcount usage

"Damian" wrote in message

> Hello all,
>
> I have code that in some cases works with recordCount and others doesnt
> (always returns -1 even if there are records).
>
> Is there a rule about using recordcount or alternative other than looping
> through a recordet and counting manually?
>
> Thanks

In general, if a query returns a recordset that contains any records
it will have a RecordCount = 1 until you do a MoveLast. If it does
not contain any records, then the RecordCount property will
return a runtime error. Here's a function which may help.
If it returns True then RecordCount will be available, otherwise
the query failed.

Private Function HasRecords(RS As RecordSet) As Boolean
If Not (RS.EOF And RS.BOF) Then
HasRecords = True
RS.MoveLast
RS.MoveFirst
End if
End Function
 >> Stay informed about: Recordcount usage 

"Norm Cook" wrote in message

> "Damian" wrote in message
>
>> Hello all,
>>
>> I have code that in some cases works with recordCount and others doesnt
>> (always returns -1 even if there are records).
>>
>> Is there a rule about using recordcount or alternative other than looping
>> through a recordet and counting manually?
>>
>> Thanks
>
> In general, if a query returns a recordset that contains any records
> it will have a RecordCount = 1 until you do a MoveLast. If it does
> not contain any records, then the RecordCount property will
> return a runtime error. Here's a function which may help.
> If it returns True then RecordCount will be available, otherwise
> the query failed.
>
> Private Function HasRecords(RS As RecordSet) As Boolean
> If Not (RS.EOF And RS.BOF) Then
> HasRecords = True
> RS.MoveLast
> RS.MoveFirst
> End if
> End Function
>
>

OK Thanks - will give that a go. Although I think I already did this and it
still didnt give me the right recordcount ... will try the above once more
and let you know if I have any problems...
 >> Stay informed about: Recordcount usage 

"Norm Cook" wrote in message

> "Damian" wrote in message
>
>> Hello all,
>>
>> I have code that in some cases works with recordCount and others doesnt
>> (always returns -1 even if there are records).
>>
>> Is there a rule about using recordcount or alternative other than looping
>> through a recordet and counting manually?
>>
>> Thanks
>
> In general, if a query returns a recordset that contains any records
> it will have a RecordCount = 1 until you do a MoveLast. If it does
> not contain any records, then the RecordCount property will
> return a runtime error. Here's a function which may help.
> If it returns True then RecordCount will be available, otherwise
> the query failed.
>
> Private Function HasRecords(RS As RecordSet) As Boolean
> If Not (RS.EOF And RS.BOF) Then
> HasRecords = True
> RS.MoveLast
> RS.MoveFirst
> End if
> End Function
>
>

I was using RS.Movelast but nor adding RS.Movefirst afterwards and it still
worked in some places. Is there a potential problem with that? Have now
added the RS.MoveFirs to all the statments just in case!
 >> Stay informed about: Recordcount usage 

"Damian" skrev i meddelandet

> Hello all,
>
> I have code that in some cases works with recordCount and others doesnt
> (always returns -1 even if there are records).
>
> Is there a rule about using recordcount or alternative other than looping
> through a recordet and counting manually?
>
> Thanks
>
>
..recordcount only works with certain drivers, cursors etc.. If 'wrong' it
returns -1.
Try using dbOpenDynamic and don't set any cursor, ie use default cursor.

/Henning
 >> Stay informed about: Recordcount usage 

"Damian" wrote in message

> Hello all,
>
> I have code that in some cases works with recordCount and others doesnt
> (always returns -1 even if there are records).
>
> Is there a rule about using recordcount or alternative other than looping
> through a recordet and counting manually?
>
> Thanks
Have you ever resolved this RecordCount question?

I have the same problem after opening a recordset. Have you seen any
working examples?
 >> Stay informed about: Recordcount usage 

"James" wrote in message

>
> "Damian" wrote in message
>
> > Hello all,
> >
> > I have code that in some cases works with recordCount and others doesnt
> > (always returns -1 even if there are records).
> >
> > Is there a rule about using recordcount or alternative other than
looping
> > through a recordet and counting manually?
> >
> > Thanks
> Have you ever resolved this RecordCount question?
>
> I have the same problem after opening a recordset. Have you seen any
> working examples?
>

Are you asking about DAO or ADO?

With DAO it is difficult to provide a "hard rule" on the validity of the
RecordCount property as it is dependent on the options used in opening the
recordset and environment in which DAO is being used. For example, a direct
query on a local database may give different results compared to a query
performed by a VB app against a linked-table in MSAccess.

But a General Rule is the Recordcount is the number of records that have
been "READ" from the recordset.
"How to: Count the Number of Records in a DAO Recordset"
http://msdn.microsoft.com/en-us/library/bb243791.aspx

A simpler solution is to use COUNT() in your query ...
Set rs = db.OpenRecordSet("SELECT colA, colB, Count(*) As cnt FROM
tableJunk, <options>) ' air code
Debug.Print rs!cnt

With ADO, whether or not the RecordCount is valid will depend entirely on
the cursor type you used. "-1" indicates "RecordCount is not supported for
this query".

You shouldn't use a RecordCount to just 'step' through a query. Simply
'loop' through the recordset using While NOT rs.EOF, etc. If you need it
then specifically query for it, or count as you step through the records.

hth
-ralph
 >> Stay informed about: Recordcount usage 

"Ralph" wrote in message

>
> "James" wrote in message
>
>>
>> "Damian" wrote in message
>>
>> > Hello all,
>> >
>> > I have code that in some cases works with recordCount and others doesnt
>> > (always returns -1 even if there are records).
>> >
>> > Is there a rule about using recordcount or alternative other than
> looping
>> > through a recordet and counting manually?
>> >
>> > Thanks
>> Have you ever resolved this RecordCount question?
>>
>> I have the same problem after opening a recordset. Have you seen any
>> working examples?
>>
>
> Are you asking about DAO or ADO?
>
> With DAO it is difficult to provide a "hard rule" on the validity of the
> RecordCount property as it is dependent on the options used in opening the
> recordset and environment in which DAO is being used. For example, a
> direct
> query on a local database may give different results compared to a query
> performed by a VB app against a linked-table in MSAccess.
>
> But a General Rule is the Recordcount is the number of records that have
> been "READ" from the recordset.
> "How to: Count the Number of Records in a DAO Recordset"
> http://msdn.microsoft.com/en-us/library/bb243791.aspx
>
> A simpler solution is to use COUNT() in your query ...
> Set rs = db.OpenRecordSet("SELECT colA, colB, Count(*) As cnt FROM
> tableJunk, <options>) ' air code
> Debug.Print rs!cnt
>
> With ADO, whether or not the RecordCount is valid will depend entirely on
> the cursor type you used. "-1" indicates "RecordCount is not supported for
> this query".
>
> You shouldn't use a RecordCount to just 'step' through a query. Simply
> 'loop' through the recordset using While NOT rs.EOF, etc. If you need it
> then specifically query for it, or count as you step through the records.
>
> hth
> -ralph

Ralph, your Count(*) sql above produces an error unless you include the
'Group By' clause and then rs!cnt returns the value 1.

IMV the most reliable method to return a true recordcount is
If not rs.eof then
rs.movelast
rs.movefirst
end if

Then you can use the construct
for i = 1 to rs.recordcount
...do whatever
rs.movenext
next
which is much faster than
do while not rs.eof etc.
 >> Stay informed about: Recordcount usage 

"Norm Cook" wrote in message

<snipped>
> >
> > A simpler solution is to use COUNT() in your query ...
> > Set rs = db.OpenRecordSet("SELECT colA, colB, Count(*) As cnt FROM
> > tableJunk, <options>) ' air code
> > Debug.Print rs!cnt
> >
<snipped>
>
> Ralph, your Count(*) sql above produces an error unless you include the
> 'Group By' clause and then rs!cnt returns the value 1.
>
> IMV the most reliable method to return a true recordcount is
> If not rs.eof then
> rs.movelast
> rs.movefirst
> end if
>
> Then you can use the construct
> for i = 1 to rs.recordcount
> ...do whatever
> rs.movenext
> next
> which is much faster than
> do while not rs.eof etc.
>

You're absolutely correct, and thanks for catching that.
In my haste, I confused ADO/DAO and was thinking along the lines of using
MSAccess's "DBCount" or a TableDef.Recordcount, ... (My excuse is the
lateness of the hour. <g>)

The "General Rule" then becomes "the Recordcount is the number of records
that have been "READ" from the recordset. So a total count is only reliable
after all records have been "touched".

-ralph
 >> Stay informed about: Recordcount usage