The_Hitcher
asked on
VBA Access recordset counting only 1
Hello
I've got a problem with a recordset variable that I am using for a loop. The code is as follows:
Set rst = dbs.OpenRecordset("SELECT * FROM LUL_Devices")
Counter = rst.RecordCount
While Counter <> 0....
...rst.MoveNext
Counter = Counter - 1
Although the table LUL_Devices has 5,625 records in it, rst.RecordCount is only returning 1, and so the loop is only updating one record. Can anyone advise why only 1 record is being stored in the recordset.
Many thanks in advance.
I've got a problem with a recordset variable that I am using for a loop. The code is as follows:
Set rst = dbs.OpenRecordset("SELECT * FROM LUL_Devices")
Counter = rst.RecordCount
While Counter <> 0....
...rst.MoveNext
Counter = Counter - 1
Although the table LUL_Devices has 5,625 records in it, rst.RecordCount is only returning 1, and so the loop is only updating one record. Can anyone advise why only 1 record is being stored in the recordset.
Many thanks in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes, a movelast will make the recordset count accurate.
JET decides based on a number of factors how quickly it will populate a recordset. When it first opens, the count will be 1 to indicate that there are records. In somecases, the count will then be updated when JET is finished, but often it is not.
Instead of relying on recordcount, use BOF and EOF for loops. This avoids doing the movelast, which is costly performance wise.
Only do the movelast if you really need an accurate recordcount and event then, consider doing a SELECT COUNT(*) on the table, which is highly optimized.
JimD.
JET decides based on a number of factors how quickly it will populate a recordset. When it first opens, the count will be 1 to indicate that there are records. In somecases, the count will then be updated when JET is finished, but often it is not.
Instead of relying on recordcount, use BOF and EOF for loops. This avoids doing the movelast, which is costly performance wise.
Only do the movelast if you really need an accurate recordcount and event then, consider doing a SELECT COUNT(*) on the table, which is highly optimized.
JimD.
ASKER