Count how many in Recordset

[glipuma]

hi - i'm opening up an access db and want to have a count of all the records in the recordset, here's part of my code getting that recordset:

sql = "SELECT * from Job"

Set fEmpRS = fEmpDB.OpenRecordset(sql)

is there a set way of knowing how many are in a recordset? thanks!

 

[MikeBronner]

Sure thing!

Try this:

Dim nRecordCount as Integer

sql = "SELECT * from Job"
Set fEmpRS = fEmpDB.OpenRecordset(sql)

nRecordCount = fEmpRS.RecordCount

Hope this helps,
Good luck!

michael Bronner

 

[EdRev]

i belive you need to do a movelast first before you can get a recordcount. you can try this:

if not(fEmpRS.eof) then
fEmpRS.movelast
nRecordcount=fEmpRS.recordcount
fEmpRS.movefirst
end if

hope this'll help.

edrev

 

[MikeBronner]

I don't mean to be argumentative, however, I have not yet experienced that problem (that you need to move to the end of the record set first).

As far as I understand it, RecordCount is an attribute independant of which record the control is at, and it is assigned at the time when the records are read in from the database.

I could see what you mean, in the case of CurrentRecord (but I don't believe that function exists for a recordset, only for databound objects).

Please let me know if I'm wrong, I'm always eager to learn new things!

Best Regards,
Michael Bronner

PS: No offensiveness intended.

 

[sparrow]

I normally use .RecordCount for my recordsets.
What I have found is

When I open the record set RecordCount = 0 or 1
If it is 1 then MoveLast resets RecordCount the the number of recordss in the record set

Hope this is helpful

Paul

 

[ciaranr]

Michael Bronner
The method adopted by edrev is more pertinent with ADO. A direct corollory to this is to check for the existence of records in a recordset. Sometimes checking for recordcount > 0 is not always accurate. I adopt a tip passed to me. Namely;
If not(adorecset.bof = true and adorecset.eof = true) then
we have data !
so .movelast
then .recordcount
end if

Have Fun
Ciaran

ciaran
ciaranr@albany.jrc.net.au

 

[jmarler]

If you only want a count of all records, why not just use this . . .

Select Count(*) From Job

This SQL will only have to return one number rather thatn the entire recordset and will perform much better. Also, you will not have the issue of having to movelast and then movefirst to get the accurate recordcount.

Michael Bronner,
Yes, in some cases you do need to move to the end of the recordset and back again in order to get an accurate recordcount. This normally depends upon what cursor type you have selected and the location of the curson (client side or server side). IF you want more info an this, let me kow and I'll post a FAQ if one does not already exist.

- Jeff Marler B-)

 

[MikeBronner]

Jeff,

Thank you! Your information has been most insightfull. I was using it with ADO just fine, and was beginning to wonder about things, hehe .

Please do post a FAQ. That might clear up some more issues.

Best Regards,
Michael Bronner

 

[tls]

The answer depends one whether you are using ADO or DAO API
If ADO the count is retuned as part of the recordset or cursor as a long.
lrcount = adoRs.recordcount
If DAO the recordset is not really populated until you movelast.
if you as for daoRs.recordcount and you have records you will always get 1 until you movelast.

 

[MikeBronner]

Thanks TLS!
You answered my question.

I have been using ADO, so I never encountered this problem before.

 

[jmarler]

MichaelBronner,
No . . . tls is incorrect (sorry tls). ADO will return a -1 for the record count when you use certain server side cursors. If you don't believe me, try the following code . . .
It uses ADO 2.6 running in VB6 under NT. The database was SQL Server 7.0 and the cursor location was server side (the default). If you use adOpenDynamic or adOpenForwardOnly, you will get a -1 for the recordcount. If you use adOpenKeyset or adOpenStatic, then the record count will return the correct value. This behavior is the SAME for both ADO and DAO. This has to do with where the cursor reside and what the intended cursor behavior is. The reasons that ADO will return a -1 is the same same reasons why DAO will return a -1 (by the way, not all DAO recordsets returned a record count of -1 . . . once again, it depends on the cursor type).

==========================================================   
Dim rs As ADODB.Recordset
Dim objConnection As ADODB.Connection

'** Create the connection and recordset objects.
Set objConnection = New Connection
Set rs = New ADODB.Recordset
    
'** Open the connection
With objConnection
    .Open "DSN=Test"
End With
    
'** Set the cursor location and open the cursor.
With rs
    .CursorLocation = adUseServer
    .Open "BogusData", objConnection, adOpenDynamic    
End With

MsgBox "Record Count:=" & rs.RecordCount
==========================================================

If you are still interested, I will explain further why this is . . . if you want to go with tls answer, then I'll drop it . . . either way, try it for yourself.
- Jeff Marler B-)

 

[MikeBronner]

Thanks,

So in order to avoid this problem, use OpenKeySet with Cursor Client side. Noted .

Also, I'm not quite clear on what the cursor location does. What is the difference between a client side and a server side cursor?

Again, thanks for bearing with me