We help IT Professionals succeed at work.

DAO RecordCount

jbauer22
jbauer22 asked
on
Medium Priority
682 Views
Last Modified: 2010-05-18
I'm trying to get a Count of the records "rstTemp".  The problem seems to be when I use the Where clause in the SQL source.  If I just specify the Table [Qry-Export Summary] as the source or Select * from [Qry-Export Summary] I am able to get a record count.  When I insert the Where clause the record count returns 1 when I know there are more records.

Suggestions?

Sub Temp()

Dim x
Dim db As Database
Dim rstTemp As Recordset

'--- The following only returns a record count of 1?
Set rstTemp = db.OpenRecordset("Select * From [Qry-Export Summary] Where LAYOUT_ID = 'migl606r.sqr'")

x = rstTemp.RecordCount

End Sub
Comment
Watch Question

Try this immediately following your Set ...

With rstTemp
  If Not .Eof
    .MoveLast
    x=.RecordCount
  End If
End With
CERTIFIED EXPERT

Commented:
i'm not a fan of using .movelast. if you have a HUGE recordset, it can take FOREVER.

another alternative is to open two recordsets (or one right after the other). the first recordset simply returns the COUNT of the SELECT statment, the next recordset actually returns the SELECT statement.

so your code would look like:

Sub Temp()

Dim x
Dim db As Database
Dim rstTemp As Recordset

'this sets the variable x to the count
x = db.openrecordset("SELECT COUNT(*) FROM [Qry-Export Summary] WHERE Layout_ID = 'migl606r.sqr'").fields(0)

'--- The following only returns a record count of 1?
Set rstTemp = db.OpenRecordset("Select * From [Qry-Export Summary] Where LAYOUT_ID = 'migl606r.sqr'")

End Sub

dovholuk
dovholuk, I concur totally.  Count is more efficient, especially with an ungodly large recordset.
Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer
CERTIFIED EXPERT

Commented:
<listening..>

Author

Commented:
My recordset would only have 3 rows a max.  Thanks.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.