.Recordcount again....

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
        Try
            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
            dbConnGetDBRecordCount.Open()
            Dim dbDR As OleDb.OleDbDataReader = dbCommandGetDBRecordCount.ExecuteReader
            While dbDR.Read
                tmpRecCountr = tmpRecCountr + 1
            End While
            dbConnGetDBRecordCount.Close()

        Catch err As System.Exception
            Support.glbErrorMessage = "ERROR: GetDBRecordCount - " & err.Message & vbCrLf
            MsgBox(Support.glbErrorMessage, MsgBoxStyle.Critical, "- ERROR -")
        End Try
        Support.glbRecordCount = tmpRecCountr
MyLifeServicesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Arthur_WoodCommented:
What is the text of

 Support.glbString

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.

AW
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rpkhareCommented:
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.
0
MitzsCommented:
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.
 
 
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.