Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


.Recordcount again....

Posted on 2008-11-10
Medium Priority
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

Arthur_Wood earned 2000 total points
ID: 22927825
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.


Expert Comment

ID: 22928173
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.

Expert Comment

ID: 22928877
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.

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

578 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