Error with OpenRecordSet (count is wrong)

[Guest]

Hello all,

This problem is driving me crazy and I can't find any reason for the
problem. Hopefully someone can point me in the direction of a solution.

I'm getting an error when I try to use OpenRecordSet to copy the values into
Excel. I've narrowed down the problem to the number of recordsets that it's
retrieving, but I can't figure out WHY there's an error with the number of
recordsets.

I have a table with 6000+ records. If I create a query (qryTest) called
"SELECT * from tblFailures", i get the 6000+ records.

If I set a record as such: Set rst = CurrentDB.OpenRecordset("SELECT * FROM
qryTest") and then check the recordcount, I get a value of 1.

But, if I modify the query for a subset of the data in the table, I get all
the records. For example:

I have a table with 6000+ records. If I create a query (qryTest2) called
"SELECT * from tblFailures WHERE tblFailures.DESC like '*coredump*'", i get
about 1000+ records.

If I set a record as such: Set rst = CurrentDB.OpenRecordset("SELECT * FROM
qryTest2") and then check the recordcount, I get a value of 1000+

I'm really confused. Not sure why one retrieves the correct number of
records and one doesn't. If I need to reply with additional code, let me
know.

If anyone has any suggestions on the cause of the error, please let me know.

Thanks,
Jay

 

[Guest]

I found the solution, but I have no idea why it works the way it works.

rst.MoveLast
rst.MoveFirst

if I use that before the export to Excel, the recoundcount works properly,
and eveything gets copied over correctly.

Cheers,
Jay

 

[Guest]

I found an old post that explained the issue.

Carry on, nothing to see here.

 

[Guest]

It is because when you open the recordset, it has not yet fully populated the
recordset. The movelast causes the recordset to fully populate because it
has to go to the last record in the recordset.

 

[Guest]

Hi

rst.MoveLast is not recomended as a method of forcing a correct record count
as (according to Access documentation) it severely hinders performance. I
guess this is because it forces the entire RecordSet to be populated
needlessly.

Better to use SQL Count:

rstCount = CurrentDb.OpenRecordset("SELECT Count(ID) as RecordCount FROM
table")

rstCount!RecordCount will return the number of records.

Cheers.

BW