Access2007 slow, what can I do?

[Mark Andrews]

I have an Access2007 application that in general works very well. I have
been testing it on various types of computers. I noticed on a fairly old XP
machine (750MB RAM) I have an issue on one form where it takes 6 to 17
seconds to open.

The form displays contact information and has a tab control with 7 tabs.
About 4 of them have subforms showing related records in a datasheet and
there is also a graph control on one tab.

On most newer computers this form takes about 2 seconds to open.

On this older computer it ranges from 6 to 17 seconds. I also noticed a
slow down on another form with only 3 tabs and a graph control. So I'm
thinking it's the graph that sometimes slows things down. The application
doesn't have much data at this point.

Any tips on any Access settings I could tweak to improve performance? Also
any tips on what aspect of the machine is causing the slowness (disk speed,
memory etc...)?

Access2007 runtime environment.

Thanks in advance,
Mark

 

[Tom Wickerath]

Hi Mark,

See my article:
Implementing a Successful Multiuser Access/JET Application
http://www.accessmvp.com/TWickerath/articles/multiuser.htm

Pay particular attention to the sections on Name Autocorrect, Subdatasheets,
Persistent Connections, Indexes, Speed up Tab controls, and using Jet Show
Plan. The section subtitled Speed up Tab controls shows you how to implement
a load-on-demand technique, so that precious network resources are not being
expended to load data into subforms that the user may never visit.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[GBA]

well - assuming the 4 subforms show a record source in their property, then
they are calling their data, even if the user does not select the tab and
doesn't want/need to see it. So to avoid this you would assign the record
source to the subform when the tab is clicked. Ultimately what this does is
allow the initial main form to open quicker but puts perhaps a little delay
in the tab opening...its a trade off - - - but if they are 4 tabs of rarely
needed data, and the record sources are complex queries - it could well be
worth it...

When the subform is a straight record set of a table or simple query - there
isn't much time involved unless the volume of data is huge and the split db
has a slow LAN.... so I don't bother with this record source manipulation
unless I know it is a candidate for slow action....

I believe DLookUps/DCounts tend to be slow candidates - not always a slow
cause but a candidate to keep an eye on and like the subform - I try to
trigger it on an 'as needed' basis and not generally when opening a form.....

as to hardware - seems like every thing contributes....

 

[John Spencer]

I would say that one thing that will cause a slow down is that you have a
minimal amount of memory for running Access 2007. As a guess you are getting
a lot of paging going on where RAM is being freed up by forcing part of the
contents onto the hard drive until needed.

If at all possible, I would spend some money to upgrade the RAM on the
computer to 2 gigs. I was just on-site at one of my customers and they were
running several computers with just 512K of RAM (XP and Office 2003).
Performance was barely tolerable with only one application open. As soon as
they opened a second application, the performance slowed to a crawl (for
example, 20 to 30 seconds to open windows explorer window).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Mark Andrews]

Very nice I'll look over everything.
Thank you,
Mark

 

[Mark Andrews]

Thanks for the information. I do have a dlookup or two in the mix as well.

Mark

 

[Mark Andrews]

Yea that was my mother's computer. However my fear is that my software
might end up on lots of computers as slow as this computer. I did see the
same form sometimes open in 6 seconds vs 17 which might be disk getting more
involved.

Thanks for the info,
Mark

 

[David W. Fenton]

If at all possible, I would spend some money to upgrade the RAM on
the computer to 2 gigs. I was just on-site at one of my customers
and they were running several computers with just 512K of RAM (XP
and Office 2003).

I upgraded my laptop from 512MBs to 2GBs about 18 months ago. It
cost $40. It was like getting a new computer.

 

[Paul Shapiro]

I have a client who had noticed that moving from Access 2003 to Access 2007
slowed the opening of large forms by a factor of 2. Upgrading from 1GB ram
to 2GB eliminated the slowdown and now Access 2007 performance is about the
same as 2003. I seem to remember that Access 2003 needed about 1GB, so I
don't think you'll get decent performance with 512MB even with Access 2003.
Either add ram or eliminate the large forms.

 

[Mark Andrews]

Thanks. I think I'll indicate that 1GB RAM or more is recommended. A few
years back 512MB was common so I'm thinking I might run into many customers
with this situation.

Mark