Solved

Access Datasheet view with too many controls

Posted on 2015-02-23
12
233 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 50

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 37

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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 50

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 37

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 50

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

751 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