Strange problem with recordsets

[victorh]

Hello,

I have another problem and I hope someone knows the reason...

In some module, I write :

Dim R As Recordset
Set R = CurrentDb.OpenRecordset("SELECT * FROM MY_TABLE")

This "MY_TABLE" has several records and the resulting recordset should have all in it, right? But as a result, only the first record of the query is in the set ( R.RecordCount = 1 ), why?!

Did someone else encounter this problem? Any help would be really appreciated!

Regards

Victor

 

[RuralGuy]

Access wants to get back to the application as quickly as possible and as a result only populates the RecordSet as it is needed. In order to get an accurate RecordCount you would need to do a .MoveLast so that Jet will populate the entire RecordSet.

 

[llkhoutx]

Just move through the recordset as you need records. Ordinarily, you'd

r.movefirst
do until r.eof
'execute some code here
r.movenext
loop

 

[DB7]

As both above said.
Simply put, if it's just the record count you want then,
Dim R as DAO.Recordset 'do distinguish
Set R = CurrentDb.OpenRecordset("SELECT * FROM MY_TABLE")

R.moveFirst
R.MoveLast 'move both ways is more reliable
intCount = R.RecordCount

R.Close: Set R = Nothing

NOW, if it's just the recordcount that you want then,
I would suggest using the DCount() aggregate function, not opening a recordset!!!

 

[victorh]

You exactly helped my problem. And I didn't just need the record count but relied on it being correct from the start.

Now I know better... thanks to all of you!

Victor


PS : This forum is really one of the best I've seen in terms of getting help etc.