Acquiring an accurate recordcount.

Acquiring an accurate recordcount.

Post by Don » Thu, 14 Aug 2003 21:37:41



I am using an Access Project using SQL Server 2000 and am
having a difficult time getting accurate record counts of
large recordsets (over 1000 records). I use the following
script to populate a caption on a form but for those large
recordsets the count displayed varies.

Public Sub prcPostRecordCount()
    Dim rst As ADODB.Recordset
    Dim lngCount As Long

    Set rst =
      Me.frmSD_RepairParts_subform.Form.RecordsetClone

    lngCount = IIf(rst.EOF, 0, rst.RecordCount)

    Me.lblMessage.Caption = lngCount & " Item(s) Fit The
        Selected Criteria"

    rst.Close
    Set rst = Nothing
End Sub

Is there a way to make the code wait until the recordset
is entirely populated before displaying the recordcount?

Thanks in advance,
Don

 
 
 

Acquiring an accurate recordcount.

Post by Matt » Thu, 14 Aug 2003 22:51:53


I think the only way is to do an rst.movelast, which forces the cursor to
move to the end of the recordset, thus populating the rst.RecordCount.

Problem with this is that if you did have say 100,000 records, that move
could take a few seconds.

It is the only way to retrieve an accurate record count though, unless you
make another SQL call to get the # of records.

- matt -
www.core49.com


Quote:> I am using an Access Project using SQL Server 2000 and am
> having a difficult time getting accurate record counts of
> large recordsets (over 1000 records). I use the following
> script to populate a caption on a form but for those large
> recordsets the count displayed varies.

> Public Sub prcPostRecordCount()
>     Dim rst As ADODB.Recordset
>     Dim lngCount As Long

>     Set rst =
>       Me.frmSD_RepairParts_subform.Form.RecordsetClone

>     lngCount = IIf(rst.EOF, 0, rst.RecordCount)

>     Me.lblMessage.Caption = lngCount & " Item(s) Fit The
>         Selected Criteria"

>     rst.Close
>     Set rst = Nothing
> End Sub

> Is there a way to make the code wait until the recordset
> is entirely populated before displaying the recordcount?

> Thanks in advance,
> Don


 
 
 

1. Accurate recordcount

Hi,

We are running a data warehouse. We load data from other
systems each night and have to report on data activities.

In the beginning, we retrieved the record count for each
table using what's in the sysindex table. We set the
database to automatically update and create statistics.
The counts were consistently accurate.

But lately, without the auto update and create statistics
being changed, the recordcount in the sysindex table is
no longer accurate. It will be difficult for us to do
a "select count(*)" since the tables are large, some have
more than 100 million records.  

How can we get an accurate record count efficiently?

Thanks, Anna

2. HELP: DUMP/LOAD problem

3. DAO Recordcount vs. ADO Recordcount

4. FileMaker Portals and Instant Web Publishing

5. My view is not accurate.

6. Help: Connecting VB5/6 ADO to ASE 11.0.3.3 (Linux)

7. net_address column in sysprocesses not accurate

8. ADSM and Onbar

9. is Datetime accurate milliseconds?

10. Best method of calculating accurate execution time of a Query

11. How to get accurate space utilization on 6.5?

12. Need to determine the size of the database but sp_spaceused is not accurate

13. Providing accurate progress for lenghty SQL stored procedures