How can I get record counts faster?

[hollering]

I have a bunch of tables that are tied to a table of quotes. On my main quoting form I have tabs for each type of item and I want them to say how many items are related to that quote. So I found myself using a whole bunch of chunks of code that look like the following:

    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT MiscID FROM tblQuotesMisc WHERE QuoteNumber = '" & Me.QuoteNumber & "'", dbReadOnly)
    If rs.BOF And rs.EOF Then
        Me.tabMisc.Caption = "Misc Charges (0)"
    Else
        rs.MoveLast
        Me.tabMisc.Caption = "Misc Charges (" & rs.RecordCount & ")"
    End If
    Set rs = Nothing

It works great but it makes the screen flicker noticeably.

I want to know if there's a way to do this faster. I recently converted this database to Access 2003, would ADO be faster?

 

[WayneRyan]

Eric,

Just set their ControlSource to =DCount("[SomeField]", "YourTable")

Wayne

 

[Bat17]

ADO will probably be slower than DAO when working within Access.

You could try using a Grouped qurey, like this I think

Dim strSql As String
strSql = "SELECT Count(tblQuotesMisc.MiscID) AS CountOfMiscID FROM tblQuotesMisc WHERE (((tblQuotesMisc.QuoteNumber)='" & Me.QuoteNumber & "'));"
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(strSql, dbReadOnly)
Me.tabMisc.Caption = "Misc Charges (" & rs.CountOfMiscID & ")"
Set rs = Nothing

if the screen flicker you could use Echo to stop the sreeen redrawing until you are ready but make sure you code is solid before trying it as a break in code will end up with a screen 'freeze'

Application.Echo False
......
Application.Echo True

HTH

Peter

 

[workmad3]

I would personally do it as an ADODB connection like so

Dim db As New ADODB.Connection
Dim rs As ADODB.RecordSet

db.ConnectionString = [I]ConnectionString[/I] 
db.Open

SQL = "SELECT Count(Primary Key) As NoRecords FROM table"

Set rs = db.Execute(SQL)

MsgBox "Number of records is: " & rs.Fields("NoRecords").Value

As my experience is that a db.execute command runs faster than a general purpose query command, and a DAO database cant do SQL SELECTS in an execute.

 

[modest]

I like Bat's suggestion best on turning off the painting until done processing:

  'Declare variables
    Dim rs      As DAO.Recordset
    Dim strSQL  As String

    strSQL = "SELECT MiscID FROM tblQuotesMisc WHERE QuoteNumber = '" & Me.QuoteNumber & "'"

    Set rs = CurrentDb.OpenRecordset(strSQL)

    If Not (rs.BOF And rs.EOF) Then rs.MoveLast

  'Turn off screen painting
    Application.Echo False
    DoCmd.HourGlass True

    Me.tabMisc.Caption = "Misc Charges (" & rs.RecordCount & ")"

  'Turn screen painting back on
    DoCmd.HourGlass False
    Application.Echo True

  'Clean up variables
    rs.Close
    Set rs = Nothing

rs.RecordCount will always return a number (even if there are no records). You just can't use .MoveLast or any move when there are no records (because there's no place to go)

 

[hollering]

Thanks

Is there any reason I should not use DCount()? It seems to work pretty well, and it would simplify the code a lot over using the recordsets. I could still stop the form from redrawing until I have my values.

I just was curious since nobody who responded after WayneRyan used that suggestion if there was some reason it's not as good as using a recordset.

Thanks for the help everyone...I'll be trying these out tonight.

-----------------------------------
Added 15 minutes later
-----------------------------------

Okay so after I wrote this I decided I couldn't wait to try it out. I used DCount along with Application.Echo. My code is much more readable and the screen flicker is no more! Still, if there is any reason I should not use DCount instead of a recordset then please let me know. You guys are great, thanks for the help!

 

[modest]

I guess it has a matter of impact on how your database is split (Front End/Back End)

"Use of DSum, DCount, DLookup, etc in form after splitting
One recent posting indicated that performance really slowed down after splitting the MDB into a FE/BE. Turns out the problem was using a DCount in the form. Replace these with your own custom function. Thanks to Susan for posting the solution to her problem.. (2003-11-06)" Here

 

[hollering]

Modest -- I got to read your post right after editing the other one. I was debating as to whether or not I should split this database into a FE/BE. I think I'm going to keep the recordsets just to be safe. The speed was not really an issue as much as the flicker. Thanks for the quick reply.

 

[mresann]

The solution to the flickering problem has little or nothing to do with the method of record count extraction. I have similar flickering problems with unattached labels and images on my tab pages themself. I am guessing you have Windows XP and Access 2002 or 2003, as the problem seems to be related to those items.

You may try referencing this article. In all my tab pages on my projects I have converted all unattached labels (that is, labels not attached to another control) to text boxes themselves. It's a hassle at times, but I really don't like the flickering.

Flickering Labels on Tab Controls

 

[hollering]

mresann,

Take note of the post above where I tried Bat's tip of keeping the form from redrawing until I want it to. This took care of the flickering problem completely for me.

 

[modest]

Mresann, I have viewed that page before and it is a separate issue, mostly related when users have WinXP themes. Though it is good to look at, the problem wasn't a result of the calculation as that is almost instantaneous, the problem had to do with printing of the screen.

To get around it, hollering was advised to turn off the screen printing, then make the changes, then view the changes by turning the screen printing back on. Thanks everyone who posted.

 

[mresann]

OK, sorry for the additional information. I didn't see that the problem of flickering was taken care of.

I won't belabor the thread any longer, but I have tried different methods repainting, even going through a couple of API methods I had used in the past for similiar purposes. I still have the problem no matter what I do, but for now I'll just stick with my text box control solution. Thanks.

 

[modest]

I never replied to this, but additional information is not always bad, as unattached labels in tab controls have been a problem for me in the past. Your solution is very helpful to many people who have that problem, however this would be better suited if it was in a thread of it's own. It would generate better search results. Just title with "Mousemove or Mouseover Flicker"