Recordset

[Andreex]

Hi.... I know this post should be at the database forum. But the thing is... this is a very simple one!!!! And.... in the database forum there are not a lot of responses!

OK.. so.... I am doing this to know what is the amount of records that a query gets:

dim record1 as recordset
 
dim a as integer
 
'base is set to a database thru ODBC
set record1=base.execute("select * from table")
 
a= record1.recordcount

but "a" will always be -1. How can I know the total of the records that a query gives me?


Thank you

[peet]

try moving last first before doin the record count

record1.movelast
record1.movefirst
a= record1.recordcount

peet

[Andreex]

Thank you peet for replying.....

It didnt work !

Help.... help!

[peet]

a question for you Andreex

is

set record1=base.execute("select * from table")

the same as

set record1=base.openrecordset("Select * from table")

?

peet

[Michael Woolsey]

That should work... is your query correct? Ie: You didn't make a mistake in it?

The reason I ask is because if you have returned records and you move to the last record that sets the .RecordCount property.

Maybe your query isn't returning anything...

Just a thought.
Michael

[Andreex]

Michael :

Thank you for helping man!

About my queries... they are working. They do get information and eveyrthing. My app is almost done. I just want to add some final details.


Peet:

humm... I dont know about that. Is it the same? I usually do it the first way. I am going to try the second way too.

[peet]

hmmm found this ...

****************************************
RecordCount Property


Indicates the current number of records in a Recordset object.

Return Value

Returns a Long value.

Remarks

Use the RecordCount property to find out how many records are in a Recordset object. The property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount. Reading the RecordCount property on a closed Recordset causes an error.

If the Recordset object supports approximate positioning or bookmarks—that is, Supports (adApproxPosition) or Supports (adBookmark), respectively, return True—this value will be the exact number of records in the Recordset regardless of whether it has been fully populated. If the Recordset object does not support approximate positioning, this property may be a significant drain on resources because all records will have to be retrieved and counted to return an accurate RecordCount value.

The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor, the actual count for a static or keyset cursor, and either -1 or the actual count, depending on the data source, for a dynamic cursor.

*********************

peet

[Michael Woolsey]

Well if all else fails you could just cycle through the recordset and keep a counter of how many records there are... something like:

Do While Not rs.EOF
  intCtr = intCtr + 1
  rs.MoveNext
Loop

The only problem with this is, if your queries take a long time to execute it will slow your application down.

Michael

[mdake]

With forward only recordsets you can only get a count after moving to the end and then moving back, this is also true of some server side cursors.

An easy way to get the count is to use a SQL statement to retrieve the number of records that meet te criteria of your recordset i.e.

Dim cn As New ADODB.Connection, rs As New ADODB.Recordset
Dim reccount As Long

cn.Open "DSN=pubs"
' retrieve the number of records
rs.Open "SELECT COUNT(*) FROM publishers", cn, , , adCmdText
' now the Recordset has one row with one field
reccount = rs(0)
rs.Close
' then retrieve the actual rows
rs.Open "SELECT * FROM Publishers", cn, , , adCmdText

Or if you are using SQL Server or a another database that supports multiple SQL statements in one query then you can use the following

Dim rs As New ADODB.Recordset
Dim reccount As Long, sql As String

sql = "SELECT COUNT(*) FROM publishers;" & "SELECT * FROM publishers"
rs.Open sql, "DNS=pubs", , , adCmdText
' the first returned Recordset contains the COUNT(*) value
reccount = rs(0)
' the second Recordset contains the actual rows
Set rs = rs.NextRecordset

[Andreex]

Thank you very much for ALL the information! !!!!

MAN!!! You are like human dictionaries!!!! All the answers worked! And it gave different points on how to solve the
problem!

Thanks also for the information about the different types
of recordsets. That was very usefull.

I am sorry I could not answer right away... I was gone!

BUT THANK YOU!!!!!!!!!!