Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access Datasheet view with too many controls

Posted on 2015-02-23
12
Medium Priority
?
250 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 52

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 40

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

 

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 52

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 40

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 52

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

879 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