.Recordcount again....

Posted on 2008-11-10
Last Modified: 2012-05-05
I have created a Windows application using an access database. Things worked OK until I got over 100,000 records. Not things have slowed tremendously.

I am looking for a more effective way to get a record count.

Here is the CURRENT code

        Dim tmpRecCountr As Integer
            Dim dbConnGetDBRecordCount As OleDbConnection
            Dim dbCommandGetDBRecordCount As New OleDbCommand()
            Dim strPath As String

            strPath = Support.glbDatabaseName
            tmpRecCountr = 0
            dbConnGetDBRecordCount = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "DATA SOURCE=" & strPath)
            dbCommandGetDBRecordCount.CommandText = Support.glbString
            dbCommandGetDBRecordCount.Connection = dbConnGetDBRecordCount
            Dim dbDR As OleDb.OleDbDataReader = dbCommandGetDBRecordCount.ExecuteReader
            While dbDR.Read
                tmpRecCountr = tmpRecCountr + 1
            End While

        Catch err As System.Exception
            Support.glbErrorMessage = "ERROR: GetDBRecordCount - " & err.Message & vbCrLf
            MsgBox(Support.glbErrorMessage, MsgBoxStyle.Critical, "- ERROR -")
        End Try
        Support.glbRecordCount = tmpRecCountr
Question by:MyLifeServices
    LVL 44

    Accepted Solution

    What is the text of


    and why are you stepping through the DataReader one record at a time to get the count of the number of records.  Is there a reason why you are not simply using SQL like

    Select Count(*) as CountOfRecords from MyTable where field = Value

    and then getting the count directly?  This would be much faster.

    LVL 8

    Expert Comment

    Rightly said above. I would recommend you do this:

    (1) Access Database can grow considerably over a period of time. Use "compact Database" feature of Access to compress the Database.

    (2) Suppose your table has a column called "RecordID". You can get Record Count like this:

    Select count(RecordID) from TableName;

    You set the CommandText property with the above query.

    (3) No need to use ExecuteReader. Only "ExecuteScalar" will do. Don't use DataReader.
    LVL 4

    Expert Comment

    Very much agree to what has been suggested above.
    If you dont want to change what you already have in place then I guess another way to do it is to create a dataset and then you can get the count as below
     Dim dataset1 As System.Data.DataSet
           dataset1= New System.Data.DataSet()
           CType(dataset1. System.ComponentModel.ISupportInitialize).BeginInit()

           dbDR .Fill(dataset1)
    Support.glbRecordCount = dataset1.tables(0).rows.count.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now