Solved

Access Datasheet view with too many controls

Posted on 2015-02-23
12
239 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 38

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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

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 38

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

627 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