Recordcount usage
Damian
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
Jeff
rs.movelast
rs.movefirst
The recordcount will then have a correct value.
Just make sure the recordset is not forward only!
"Damian" <damian....@baesystems.com> wrote in message
news:45e6aa18$1...@glkas0286.greenlnk.net...
Norm Cook
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
Damian
"Norm Cook" <normcoo...@cableone.net> wrote in message
news:12udmln...@corp.supernews.com...
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...
Damian
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!
Henning
"Damian" <damian....@baesystems.com> skrev i meddelandet
news:45e6aa18$1...@glkas0286.greenlnk.net...
returns -1.
Try using dbOpenDynamic and don't set any cursor, ie use default cursor.
/Henning
James
I have the same problem after opening a recordset. Have you seen any
working examples?
Ralph
"James" <nos...@please.com> wrote in message
news:d%ijl.318$jr7...@newsfe08.iad...
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
Norm Cook
news:elRdaqVi...@TK2MSFTNGP04.phx.gbl...
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.
Ralph
"Norm Cook" <norm...@cableone.net> wrote in message
news:O2CNVAfi...@TK2MSFTNGP03.phx.gbl...
<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
> >
>
> 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