Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Slow speed of programmatically setting control properties

Posted on 2009-04-23
12
Medium Priority
?
338 Views
Last Modified: 2013-11-28
I have recently converted a system from AccessXP to Access 2007. One of my forms periodically displays a tab which contains 120 labels (or text boxes) for which properties (NOT values) may need to dynamically changed when other values on the form are changed. The properties most commonly affected are BackColour, BackStyle, BorderStyle and Tab. Using AccessXP, the properties of all 120 controls could be changed in less than a second. With Access 2007, the same process takes 10 to 13 seconds. Has anyone a suggestion as to why this should be or, better still, some technique for making the process MUCH quicker in Access 2007.
Thanks, Tony
0
Comment
Question by:TonyWootton
  • 7
  • 4
11 Comments
 
LVL 58
ID: 24220945
<<I have recently converted a system from AccessXP to Access 2007. >>
<<Using AccessXP, the properties of all 120 controls could be changed in less than a second. With Access 2007, the same process takes 10 to 13 seconds. Has anyone a suggestion as to why this should be or, better still, some technique for making the process MUCH quicker in Access 2007.>>
  Make sure the DB has been converted (not simply opened) and that it has been compiled.
JimD.
0
 

Author Comment

by:TonyWootton
ID: 24222305
Hi Jim,
Thanks for your reply.
It has been converted (2002 to 2007) and compiled.
I attach an Accdb (renamed .txt) which gives an example of what I had though would work slowly, but it actually works faster than the the MDB. I'll prep a snippet of code that has got the problem asap.
Regards, Tony
Props.txt
0
 
LVL 58
ID: 24224083
Tony,
  If I get time, I'll load A2007 to check this out, but so far I haven't had any reason to use A2007.
<<I attach an Accdb (renamed .txt) which gives an example of what I had though would work slowly, but it actually works faster than the the MDB. >>
  I'm a little confused by that statement...I thought it was working slower then the original MDB?
  only other thing I can offer at the moment is to make sure your up to date on service packs.  I know some have seen slowness in A2007 apps, but not to the degree you've indicated.
JimD.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:TonyWootton
ID: 24224646
Hi Jim
Thanks for your reply. My apologies for confusing you! I meant that I had thought the example would work much more slowly than it did. It wasn't practicable for me to use a subset of my app code as a test harness so I selected statements that I thought were causing the problem. Since your reply, I bracketed the offending code with debug.print statements and the following fragment takes 8 seconds to execute:
    Debug.Print Now
    For idx = 65 To 68
        For jdx = 1 To 30
            str = Chr$(idx) & jdx
            If Me(str).Tag <> "Unused" Then
                Select Case rstTreat(str)
                    Case 0
                        Me(str).BackStyle = vbTransparent
                        Me(str).BorderStyle = vbSolid
                    Case 2
                        Me(str).BackColor = Prob1Colour
                        Me(str).BackStyle = vbSolid
                        Me(str).BorderStyle = vbTransparent
                    Case 4
                        Me(str).BackColor = Prob2Colour
                        Me(str).BackStyle = vbSolid
                        Me(str).BorderStyle = vbTransparent
                    Case 8
                        Me(str).BackColor = Prob3Colour
                        Me(str).BackStyle = vbSolid
                        Me(str).BorderStyle = vbTransparent
                    Case 16
                        Me(str).BackColor = Prob4Colour
                        Me(str).BackStyle = vbSolid
                        Me(str).BorderStyle = vbTransparent
                    Case 32
                        Me(str).BackColor = Prob5Colour
                        Me(str).BackStyle = vbSolid
                        Me(str).BorderStyle = vbTransparent
                    Case Else
                        Me(str).BackColor = Prob6Colour
                        Me(str).BackStyle = vbSolid
                        Me(str).BorderStyle = vbTransparent
                End Select
            End If
        Next jdx
    Next idx
    Debug.Print Now
Incidentally, I should add that the If ...... "Unused" Then means that only 90 of the potantial 120 sets of core statements within the inner loop are executed. The controls are either text boxes or labels (it doesn't seem to matter which).
Regards, Tony
0
 
LVL 58
ID: 24225530
Try this:
    Debug.Print Now

    Dim strBackColor as string
   Dim strBackStyle as string
    Dim strBorderStyle as string
 
           Select Case rstTreat(str)
                    Case 0
                        strBackColor  = vbWhite
                        strBackStyle = vbTransparent
                       strBorderStyle = vbSolid
                    Case 2
                        strBackColor  = Prob1Colour
                        strBackStyle  = vbSolid
                        strBorderStyle = vbTransparent
                    Case 4
                        strBackColor  = Prob2Colour
                        strBackStyle = vbSolid
                        strBorderStyle = vbTransparent
                    Case 8
                        strBackColor  = Prob3Colour
                        strBackStyle = vbSolid
                        strBorderStyle = vbTransparent
                    Case 16
                        strBackColor  = Prob4Colour
                        strBackStyle = vbSolid
                        strBorderStyle = vbTransparent
                    Case 32
                        strBackColor = Prob5Colour
                        strBackStyle = vbSolid
                        strBorderStyle = vbTransparent
                    Case Else
                        strBackColor = Prob6Colour
                        strBackStyle = vbSolid
                        strBorderStyle = vbTransparent
                End Select

    For idx = 65 To 68
        For jdx = 1 To 30
            str = Chr$(idx) & jdx
            with me(str)
              If .Tag <> "Unused" Then
                        .BackColor = strBackColor
                        .BackStyle = strBackStyle
                        .BorderStyle = strBorderStyle
              End If
            End with
        Next jdx
    Next idx
    Debug.Print Now
0
 

Author Comment

by:TonyWootton
ID: 24225968
Hi Jim,
Although your select clause would need to follow the "str =" statement, I was prompted to think about what you've put inside the With construct. The value of rstTreat(str) will be the same as that of the .Tag value for the corresponding control for most of the me(str) controls. Assuming the assignment of new property values takes the time, I can adjust my routine using an "If rstTreat(str)<>me(str).tag Then ....".
I'm not sure if that will work, but it'll take a lot less time than reijigging the data structure and trying an altogether different technique. I'll get back to you.
Regards, Tony
0
 
LVL 58
ID: 24226073
<<rstTreat(str)>>
 Ugh, sorry.  Thought  it was looking at  one specific field in the record rather then the field that the control was based on, so yes it needs to be in the loop, which defeats what I was trying to do.
Sorry about that :(.
JimD.
0
 

Author Comment

by:TonyWootton
ID: 24226142
Hi Jim,
No need to be sorry! Your suggestion gave me an idea, although it'll take me a while to check it out and come back to you.
Regards, Tony:)
0
 

Author Comment

by:TonyWootton
ID: 24226740
Hi Jim,
I've just tested the rstTreat value against the corresponding .Tag value and cut down the time by a factor of 4. That's tolerable until I can re-visit to do a bit more tuning. Mind you, I have no idea why the problem arises in the first place. Incidentally, I have another similar problem. I developed a routine to programatically move tab controls and all that they contain at run-time (so I can create a spread-out form in design view that is maintainable, but puts tabs in the correct positions at run-time. Works fine in 2002, but runs like a dog in 2007. Haven't solved that one yet!
Regards, Tony
0
 

Accepted Solution

by:
TonyWootton earned 0 total points
ID: 24284907
Hi Jim,
The form on which I had this problem also had another performance problem related to Access 2007. I had 5 tab controls on it which were moved at runtime. Fine with XP but diabolically slow with 2007. I re-worked the form using a sub-form to replace 3 of the tab controls and eliminate moving tab controls at run-time. This not only improved the performance of the form as a whole, but also further improved the problem which was the subject of this question so that the original 10-13 seconds went sub-1 second which is quite acceptable. I have no idea why the secondary effect should have been there, but I'll accept it as just one of life's mysteries. It tends to suggest that the Access 2007 engine requires significant performance tuning by Microsoft! Meanwhile, I'll accept your suggestion that prompted me to change direction; do please contact me if I can amplify anything that has been touched upon.
Regards, Tony
0
 

Author Closing Comment

by:TonyWootton
ID: 31574050
Hi Jim,
Although this wasn't a solution, as I said in my last comment, it prompted me to solve the problem. Thanks!
0

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

810 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