Hey all,

This has me pretty mad because it's so simple and I can't make any sense of it. I have this:


If I double-click on the query "thisnQuery" it return 319 records, but t1 = 1 when I run this code.

It's the same if I run the code for the table that thisnQuery looks at. What could be happening?

Previous troubleshooting steps:
Simplified the query to the most basic "Select" statement
Set rstTask = db.OpenRecordset("SELECT thisnQry.* FROM thisnQry;")
Added Dim db As DAO.Database and Dim rstTask As DAO.Recordset
Changed references from Microsoft Office 12.0 Access Database Engine Object Library to Microsoft DAO 3.6 Object Library
Compiled / Decompiled

None of these steps made any difference.

Thanks,

Nate

This post has been edited by n8_Mills: Mar 2 2012, 08:04 PM

This is a know issue, you have to move to the last record before getting an accurate count, so try:

<picky>

I wouldn't call it an issue, per se, but rather a feature. This was done by design, for performance reasons. ADO recordsets do something similar, if the syntax/returns are not exactly the same.

The rs is designed to not load all records up front. This keeps performance good in large tables, as the records are only loaded for a certain amount past the cursor position instead of having to gather 100k records at once, for instance. A nonzero recordcount indicates only that there is in fact records, (with DAO, I believe this is a count of the number of records that have been loaded so far, but I'm not positive on that without looking it up)

</picky>

cheers,

Issue/feature, all in the eyes of the beholder! (IMG:style_emoticons/default/sarcasm.gif)

Valid point, and one I was not aware of. I learnt, yet again, something new by being at UA!

A little more info:

Quoting Walter from a different thread:



Quoting Brent from a different thread:



Cheers,

if i need a record count, i tend to do

dcount("*",somequery"), rather than manipulate the recordset cursors, to be honest

Thanks all, learn something new every day. I guess the "Recordcount" naming is misleading, it really should be RecordNum or something like that.

Some time ago, I posted the following to my blog, which might be of some interest:

What is the fastest way to return the Record Count from an Access Query?

Edited to Add:

BTW, you do not need to move to the last record if you are opening a TableType recordset. For a local table, you can do this:

CODE

lntCount = CurrentDb.OpenRecordset("Mytable").RecordCount

For a linked table, you can do this:



which you can find here:
What is the fastest way to return the Record Count of a table?