Custom Access application slow

APD Toronto
APD Toronto used Ask the Experts™
on
Hello Experts,

15 months ago I wrote an Access application and the details screen is pretty detailed and during these 15 months I uppgraded and upgraded. The detail code is now 2261 lines and it takes around 8 second to load, which my client says that it is way to long. I know that if I rewrote this code it will probably half or less, but if there is less time consuming solution?

I have already run compact and repair and compile.


Any advice?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Architect / Application Developer
Top Expert 2007
Commented:
8 seconds.  Humm. Well, I've seen a LOT worse than that.

Many things can affect loading other than code per se. I would be pretty hard to give advice based on the small amount of info you have provided.  Imagine yourself reading this question ... how would you answer it?

I ALL 2261 lines of code executing when the app load, and if so what  ... is it doing?

mx
u can put some timers in your application to measure which part of it is slowing down the loading process. usually if u have a long list of items in drop down box, that would be very slow if u add items one-by-one. with the help of those timers, u can identify the problem very soon
I don't know if this will work for your situation but it's worth looking at. I've used this software on my VB projects for many years with excellent results. It says it works with VBA but I haven't tried it with an Access app.

http://www.aivosto.com/index.html

No points -- just a thought
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

APD TorontoSoftware Developer

Author

Commented:
I will try your suggestions, but it takes 8 seconds to open one record and they open a lot of records everyday.

I think I will also look at the form load event, but is there any format to document on paper in plain English what that code is doing, so I have it for my record?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007
Commented:
You want to look at the Record Source for the Form, and any code in the Open, Load or Current event.  Post this if you can.

Better yet, upload a sanitized version of the app that exhibits this behavior.

mx
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
There are several things that can slow performance. I am assuming that you have an application that is composed of a Backend (tables only) and a FrontEnd (everything else), and that you have place the BE on a network share, and you have installed a COPY of the FE on every machine (i.e. they're not running the FE from a network share, but instead directly from their desktop):

1) Have you turned OFF SubDataSheets? This is done in Table Design (even for Linked tables). Set the SubDataSheet property for each table to [none]

2) Do you have proper indexing on the tables? Indexing can play a big factor in performance. In general you should index any field used in a search, join, where clause, and such.

3) Are you using a lot of Subforms on your forms? These tend to slow things down quite a bit if you have lots of them, or if you're nesting them.

4) How is your app structured? I'm assuming you're doing this over a network, and if so the speed and reliability of that network plays a big role in the performance of your database. One way to test is to put the database (both the Frontend and Backend) on the same machine, and link the FE to that new BE location. If the performance is noticeably better (like a second or less to open a form vs 8+ over the network) then you can lay some of the blame on network performance.

Many other factors can affect performance, or course, but those seem to be able to make the biggest impact.


In addition to all the good suggestions above, does your form have multiple tabs?  The default behavior of tabs is to load all the tabs when the form first opens, which can be quite a performance hit.  There are published techniques for waiting to load tabs only when they're clicked.
APD TorontoSoftware Developer

Author

Commented:
Thank you all for your suggestions.

Appearantly, after upgrading from Access 2003 to 2007, the load time reduced from 8s to 4s.  I doubt tht this is a permanent fix, but I will revisit this once it becomes a problem again :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial