.RecordCount problem

[Les Isaacs]

Hi All

I have:

Dim rstS As DAO.Recordset
Dim NumberToDo As Integer
Set rstS = CurrentDb.OpenRecordset("select * from [qry bulletin recipients]")
NumberToDo = rstS.RecordCount

The problem is that [qry bulletin recipients] returns 4 records (on my test data) but rstS.RecordCount seems to evaluate to 8!

What's wrong? Hope someone can help.
Many thanks
Les

 

[Galaxiom]

I have no idea where the 8 comes from but DAO recordsets don't know how many records they have until the last record is loaded.

Do the MoveLast Method before the count and see what it returns.

 

[Les Isaacs]

Hi GlaxiomAtHome
That did it - many thanks!!
Les

PS I wonder where the 8 came from?

 

[JANR]

There is one way to get the recordcount without using .MoveLast, if you have a numeric recordID you can do something like this to force to open all records.

Function CountRec()
With CurrentDb.OpenRecordset("select * from MyTable where [ID] > 0 or [ID] < 0")
    Debug.Print .RecordCount
End With
End Function

JR

 

[Galaxiom]

There is one way to get the recordcount without using .MoveLast, if you have a numeric recordID you can do something like this to force to open all records.

Function CountRec()
With CurrentDb.OpenRecordset("select * from MyTable where [ID] > 0 or [ID] < 0")
    Debug.Print .RecordCount
End With
End Function

JR

Why would this be a better option than MoveLast (or DCount for that matter)? Why the odd Where condition?

 

[JANR]

It uses Rushmore optimisation by coersing jet to use the primarykey index to search the table, a fun discussion at UtterAccess here: http://www.utteraccess.com/forum/lofiversion/index.php/t1942623.html

JR

 

[Pat Hartman]

Select Count(*) As RecCount from MyTable;

should do the same thing and not require reading the entire table. With this query, Jet/ACE should be able to use the statistics it maintains for each table.

Select Count(somefield) As RecCount from MyTable;
isn't as efficient (and should be similar to the previous example) since it requires either an index or full table scan to evaluate all values.

 

[JANR]

If the goal is just to count a recordset then yes it would be ponitless to create a DAO.Recordset just to do a count, you might as well use Dcount() which I suspect opens a recordset in some fashion and returns the count.

But if you want to do something with the recordset when it is fully loaded then it offers you a way without moving the pointer or checking for .BOF or .EOF before you move the pointer. Rushmore must have minimum of 2 fields to filter on to kick in, so:

Set rs = Currentdb.OpenRecordset("Select * from MyTable Where SomeField = 'Bob' And SomeOtherField = 456")

and the hole recordset is availible in memory.

Set rs = Currentdb.OpenRecordset("Select * From MyTable Where Somefield = 'Bob'")

Here you have to check for .BOF and .EOF before you attemt to move the pointer, but you can trick JET to load the hole set if you use a "dummy" field which will not change the recordset like the PK >=0.

Set rs = Currentdb.OpenRecordset(Select * From MyTable Where SomeField = 'Bob' And PK >= 0")

or if you happen to have negative PK's

Set rs = Currentdb.OpenRecordset(Select * From MyTable Where SomeField = 'Bob' And (PK >= 0 OR PK < 0)")

JR