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
Solved

Access Datasheet view with too many controls

Posted on 2015-02-23
12
224 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
  • 3
  • 3
  • 3
  • +2
12 Comments
 
LVL 49

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 84
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 35

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 49

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 35

Accepted Solution

by:
PatHartman earned 500 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 49

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Microsoft Access 97 and Delphi XE2 9 53
Connecting to MSSQL server from MS Access 5 50
Rounding precision in MS Access 9 55
Importing Excel file into Access 5 25
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

840 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