Quricker way to count # of files in an ADO recordset?

[mrdod]

I've finally gottento the point (I think) where I can start tweaking things a bit and I've noticed the time it takes to count how many files I have in my recordset is quite long. Is there a quicker way to know how many files are in the recordset? Here is what I have so far.

rst.MoveFirst
Do Until rst.EOF = True
dummyvariable = rst(0)
r = r + 1
rst.MoveNext
Loop
MsgBox r & " records will be retrieved."

Any help would be appreciated.

 

[SgtJarrow]

Have a look at rst.RecordCount.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[Remou]

Yes.
[tt]rst.Movelast
msgbox "Records " & rst.RecordCount[/tt]

 

[mrdod]

I've done this but I get an error saying "Rowset does not support fetching backward."

Set rst = cnt.Execute(strSQL)
rst.MoveLast
MsgBox "Records " & rst.RecordCount

Any other thoughts?

 

[Crowley16]

I believe you have to use a keyset cursor in order to get the recordcount property...

--------------------
Procrastinate Now!

 

[Remou]

Have a look at this:

http://www.microsoft.com/technet/pr...book/c07_recordsets_and_cursors.mspx?mfr=true

 

[mrdod]

This is my first attempt using keyset. I am getting an error message saying, ODBC driver does not support the requested properties. Is it my Syntax?

strDSN = "cqar database"
cnt.Open strDSN
Set rst = cnt.Execute(strSQL)
rst.Close
rst.Open "select * from CQARNUmber", cnt, adOpenKeyset, adLockOptimistic
rst.MoveLast
MsgBox rst.RecordCount

 

[GHolden]

You can use something like this if you just want the count, will be faster...

Dim conDatabase As ADODB.Connection
Dim rstRecordSet As ADODB.Recordset

Dim strSQL As String

Set conDatabase = CurrentProject.Connection

strSQL = "SELECT COUNT(*) AS RecCount FROM Table1"

Set rstRecordSet = conDatabase.Execute(strSQL)

MsgBox rstRecordSet("RecCount")


rstRecordSet.Close
conDatabase.Close

Set conDatabase = Nothing

Obviously change the MsgBox to a variable assignment and swap Table1 for your table name.

There are two ways to write error-free programs; only the third one works.