Im using DAO for access database. I

[djmc]

Im using DAO for access database. I'm querying the database and I would take to get the count of how many records are in that recordset from the query and if there are more than 1 record to query again with a different statement.

How do I get the count of a query when it is a RecordSet object rs.RecordCount doesnt work because it only checks how many records it went thru and i don't want to make a counter and loop through it because i think there is probably another way or function in the recordset object that would return the number of records in that recordset from the query.

Thanks.

 

[jmerencilla]

i'm not familiar with DAO, though i'm familiar with ADO. i suggest you loop through your recorset and count the number of records it contain.

e.g (in ADO)

with RS
dim intCtr as Integer
intCtr = 0

while not .EOF
intCtr = intCtr + 1
.MoveNext
wend
end with


'intCtr will now contain the number of records in RS

 

[scstand]

You can try these steps for DAO:

1. Populate the Record set (say rs)
2. Check rs.EOF to ensure that atleast one record exists
3. rs.movelast
4. rs.movefirst
5. The rs.recordcount should contain correct record count now.

 

[CCLINT]

If you are going to query again, then probably the most effiv´cient way is to do the following:

SELECT COUNT(*) From TheTable

Debug.Print rs(0)

These 2 methods are fastest.

Note: the COUNT doesn't use a field name Alias - COUNT (*) AS TheRecordCount - and , because only one record and one field is returned, using the field index, rs(0), is faster than using an alias field name, (or an actual field name) as in rs("TheRecordCount&quot.
*******************************************************
[sub]General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!