size of recordset

hello!!!
i'd like to know the size of a recordset, i mean how many records it has, ... and also know the code to see if a recordset is empty
thanks, paula

objRecordSet.CursorLocation = adUseClient '<---do not forget this line

objRecordSet.RecordCount '<---to know the size of a recordset

'When the RecordCount = 0 then it is Empty

To check if a recordset is empty, use the Eof and Bof propertie. If both are true, then the recordset is empty.


if rs.eof and rs.bof then
Msgbox "Recordset empty"
end if

Be careful with the recordcount property... It will not return the correct value if using a Foward-only cursor... (it returns -1). It might also not return the correct value for a Dynamic cursor.

tahnks a lot guys/girls!
...but as u said the RecordCount returs -1... how can i know the size of it?

an other thing... i should set free the recordset??.. i mean.. should i "destroy" the recordset after used it?... 'coz it seems to keep the last value...
paula

I think you should take a look at the Ado tutorial. It is probably the best thing to start with.

http://www.xtremevbtalk.com/showthread.php?threadid=66994

tahnks a lot guys/girls!
...but as u said the RecordCount returs -1... how can i know the size of it?

an other thing... i should set free the recordset??.. i mean.. should i "destroy" the recordset after used it?... 'coz it seems to keep the last value...
paula

Here is one practice


Dim rs As ADODB.RecordSet
Set rs = New ADODB.RecordSet
rs.CursorLocation = AdUseClient '--- Do not forget this, so that your RecordCount Property wont return a -1 value.

'Some code goes here....

Set rs = Nothing

not very efficient, but always effective:


Do while not rs.eof
Teller = Teller + 1
Loop

Msgbox("Recordcount: " & Teller)


btw: to be precise about the destroying part, you must first close the recordset and then destroy it


rs.close
Set rs = Nothing

Another option is to simply do a SELECT COUNT(*) query to return the recordcount:



"SELECT COUNT(*) as total_Count FROM myTable"