Friday 22 June 2012

4. RecordCount without MoveLast in VBA


For recordsets based on queries, SQL statements, and attached tables, the RecordCountproperty returns the number of records accessed so far. When you first OpenRecordset(), Access grabs the first record, and keeps processing your code while the others load. So, if you test RecordCount immediately after you OpenRecordset, you typically get 0 (if there are no records), or 1 (if there are any, regardless of how many will load.) This does not apply to recordsets of typedbOpenTable type (the default for local tables.)

Solution:

If you need to know the RecordCount, use the MoveLast method first. This forces Access to wait while all records load, so the RecordCount reflects the entire recordset.


Hint:

Don't MoveLast unless you really need to: this will be slow with a large recordset or a recordset drawn across a network. RecordCount will always be at least 1 if records exist, so there is no need to MoveLast if you only want to know if you have records to work with.




No comments:

Post a Comment