Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access Datasheet view with too many controls

Posted on 2015-02-23
12
Medium Priority
?
246 Views
Last Modified: 2015-03-02
We currently have a Datasheet view that we have created in Microsoft Access 2013.  This Datasheet have over 63 fields, several links/buttons, and conditional formatting.

ThIs datasheet has become slow over time and when I go to analyze performance, it states that the number of controls needs to be reduced.

1. Are there a total number of Controls that is ideal for the performance on a Datasheet?
2. Is there a way to conditional format a Row in the Datasheet rather than by individual fields?
3. what other thoughts do you have to improve the performance of a datasheet?
4. We are using a split database - would converting to SQL database for the data help the performance?
5. Many of the users are accessing the database through Access Runtime.  Does this impact performance as well?


Thanks
Example-of-Datasheet.png
0
Comment
Question by:btgtech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +2
12 Comments
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40625738
I've seen this and can point at one bottleneck: Conditional formatting.

It's a dog. After(!) all records are retrieved and sorted/filtered, CF is called for each field for each row - in that sequence - where it effectively retrieves all data again and repaints the form multiple times.

There is no way around this other than using CF on as few coloumns as possible.

/gustav
0
 
LVL 85
ID: 40625877
I'd agree with Gustav regarding CF. Use it as little as you need.

I don't think the number of controls is an issue. I'd be more concerned with the hyperlinks and such you're using - while i have no hard evidence, I've seen datasheets with a LOT of hyperlinks get very, very slow. Removing the hyperlinks increased performance, but of course that also removes the functionality.

Moving to SQL Server can sometimes help, but your issue is not data-related, but rather UI-related. SQL Server is only a database engine, and has no impact on issues involving the UI.

The Runtime is no different than the Retail version in regard to performance.
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 40625918
How many rows is the datasheet returning?  Converting to SQL server will not improve performance and in fact it may make it worse if you don't limit the rows each form returns.  The whole benefit derived from switching to SQL Server is to have the server do the heavy lifting and return as few rows as possible to the FE.  If you are selecting all the rows in the table, you'll find that SQL Server may in fact be slower than Access.

As the others have indicated, CF is a dog so use it sparingly.  I would colorize only one column rather than 7 (unless they could be different).  It is not as pretty but the users can get past pretty quickly enough if performance improves.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:btgtech
ID: 40625984
Right now the datasheet is returning 42 rows.  This may be anywhere between 30 and 80 rows.

What does sparingly for CF mean?  We currently have 13 CF Columns.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40625998
Sparingly means as few as at all possible.

For a start, try moving all CF and watch the difference in performance.

/gustav
0
 
LVL 75
ID: 40626254
"Right now the datasheet is returning 42 rows.  This may be anywhere between 30 and 80 rows."
That is not very many. I don't see any major issues with that few rows.
3000-4000 then probably.

mx
0
 
LVL 39

Accepted Solution

by:
PatHartman earned 1500 total points
ID: 40626278
Has the BE been compacted recently?  If not, compact it, THEN compact the FE.  Compacting the BE reorganizes the table storage and gets rid of the dead space and updates the table statistics.  Compacting the FE forces all the querydefs to an uncompiled state so the first time they run, they will recalculate their execution plan which will use the newly updated table statistics in the BE.

If that doesn't speed up the form, make a copy and remove all the "duplicate" conditional formatting so that you have only a single column rather than multiple that are formatted to show the same information.
0
 

Author Comment

by:btgtech
ID: 40626474
are there other options to Conditional formatting that you have used?
0
 
LVL 75
ID: 40626727
" We currently have 13 CF Columns"
As a test, remove the CF from those columns ... and see how it affects performance.

Other options to do what exactly ?

mx
0
 

Author Comment

by:btgtech
ID: 40626765
Other alternatives to identify records that
1. Have issues in the database to be resolved
2. Records related to specific work centers
3. Show which records have notes
0
 
LVL 75
ID: 40626833
Of course there are always other ways to do most anything in Access ... however ...

As a test, remove the CF from those columns ... and see how it affects performance.
(make a test copy of the db)
13 columns is ... a far number regarding CF I suppose.  But you should determine the actual impact of CF ... because it could ... be something else :-)

mx
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40627525
> Other alternatives to identify records ..

Unfortunately, for datasheets these are few. Individualized colouring is not possible - except for the simple options offered by using the Format property. So you can make positive/negative number Black/red and create a green/red "Yes"/"No" for a boolean value.

/gustav
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

715 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