Access performance in design mode

[SusanV]

Hi all,

Running Access 2000 on XP Pro (512 MB ram)

While modifying a small mdb front-end piece, I've found that as I work in
the VBA Editor or in Report Design View, gradually access save and compile
time slow to a crawl, and I can pretty much forget about opening even simple
reports to Print Preview unless I want to wait several minutes. I've tried
closing Access and verifying that no processes are still running and
reloading the mdb, but still it's slow and getting slower.
Compacting/repairing doesn't make a difference, only rebooting the computer
brings the app back up to speed. It's very frustrating as this is causing me
to reboot my PC several times a day. ONLY Access seems to have this problem,
and only when in design mode / VBA Editor. I've imported all the forms
reports and modules to a new front-end mdb (which cut the file size nearly
in half) and this helped a little, but still getting this memory-leak type
behavior - although at the time I'm experiencing the slowness System Idle
Processes generally are at 85 - 98% and MSAccess.exe is perhaps ranging from
5-10%.

I have less problem with forms than reports, and the queries the reports are
based on still run just as quickly as always when run directly. I've checked
Office and Windows Update and my PC has no missing patches or service packs.

Any ideas?

Thanks in advance,

Susan

 

[Allen Browne]

Susan, it you have not done so, you might start working through the issues
that Tony Toews lists in the Access Performance FAQ at:
http://www.granite.ab.ca/access/performancefaq.htm

Several of those issues are crucial, e.g. Name AutoCorrect (number 1), and
Subdatasheet Name.

If a report is based on a crosstab query, it can take quite a while to open,
because Access must run the query to completion in order to determine the
names of all the columns available to the report. If this is the scenario
you describe, you might be able to solve the problem by listing the names in
the Column Headings property of the query, or by saving the report unbound
and assigning its RecordSource in its Open event.

Other than that, I have not experienced the problem as you describe it.
Particularly the need to reboot is completely foreign to me, other than
memory leaks associated with opening reports that contain embedded objects
repeatedly (thousands of times.) So, hopefully some of the items in the
Performance FAQ will solve your issue.

 

[SusanV]

Hi Allen and thanks for replying.

Name Autocorrect is already off.
All source queries are simple "select * from table where field = 'foo'" type
queries, no crosstabs
No embedded objects, images, OLE, nothing - just labels and textboxes.
No heavy code in reports - simple stuff like Format, UCase, that sort of
thing.

Also, database performance is fine when users are inputting or modifying
data, including myself - it's only when working with DESIGN features that it
drags down. Not a network issue as I'm working on a local copy of the
frontend mdb, linking to a local copy of the backend as well (so that an
oops won't trash the live data. )

I'm working on a couple of projects and ALL of them do this during dev, so
I'm thinking it's not a db issue but rather an application/OS/PC one. I'm
thinking it's a probably PC issue, but haven't had the opportunity to work
on a different machine for dev to test it. Next week one of my users is on
vacation, maybe I'll sit at his desk one day for dev and see if I still get
the performance degradation.

Ah well, maybe I'll get a new machine out of this <grin>

Thanks again,

Susan

 

[SusanV]

I had missed the Subdatasheet property set to [AUTO] issue, so I've run in
the code from Q261000 to set them all to [NONE], hopefully that will make a
difference.

Thanks again!

 

[Allen Browne]

Sounds very frustrating, Susan, but it's not typical of Access.

On Win XP SP2, I use I use A2003 SP1, A2000 SP3, and A97 SR2, sometimes
running 16 hour days. I exit Access to compact and backup every 4 hours, but
never reboot during the day unless there is a crash (about once in 2 weeks.)

I am a minimalist: nothing gets into the SysTray on my dev machine unless it
can justify its existence. No screen saver. Just email, and media player.


As far as the Access setup goes, Macro security is low and Sandbox mode
disabled. It flies. HTH

 

[SusanV]

Yes it is frustrating, but changing the subdatasheet to [none] seems to have
helped. Unfortunately, as I work for a small company, I wear many hats (sys
admin, project manager, marine engineering crap, web design, helpdesk, etc
etc etc) and therefore must run many apps on a single machine, and I'm due
for an upgrade. This machine *used to be* the fastest in the company but
that was 2 years ago and it's overdue for replacement. Could be worse,
though - some of our users are still running P-IIs with Win98 <grin> Of
course all they use is Word, Excel and Outlook... but still!

At home I have a dedicated dev box, no internet or even network so not even
antivirus in the background - the only thing installed on it are Access and
Visual Studio. That machine flies with less CPU or RAM than this machine
has, but that's the only thing it does.

Thanks for listening to an old lady complain! ;-)

 

[Albert D.Kallal]

To speed up design mode, try the following:

When you open the front end, the first thing to do is open a table that is
in the back end. Now, minimize the table.

Now, try designing those reports, or queries, or forms. You will notice that
all delays have gone.

Try the above, and post back....

Note that you also need your startup code to "open", or keep a so called
persistent connection when the application runs, as that will also speed up
the operation of the application. (and, of course, since we are split, then
you should distribute a mde to each desktop). And, if you are not
distribution a mde to each desktop, then you can read why here:

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

Also, since you are split, you can put a local copy of the back end on your
comptuer, and that also will fix the slow design time issue. Furhter, it is
better to work on a copy of the data anyway.

 

[SusanV]

Hi Albert,

Yes, users only get the MDE and they have local copies. Yes, I use a local
copy of the backend for dev so I don't screw up live data if I fat-finger
some SQL or code. However, I'll try opening a table prior to designing.

What do you mean by this though?;

Note that you also need your startup code to "open", or keep a so called
persistent connection when the application runs, as that will also speed
up the operation of the application.

I suppose I would put the "open" code in my startup menu form's OnLoad or
OnOpen event but not sure what code I would put as this isn't ODBC but
simple linked tables. Sorry if I'm obtuse!

Susan

 

[Albert D.Kallal]

Hi Albert,

Yes, users only get the MDE and they have local copies. Yes, I use a local
copy of the backend for dev so I don't screw up live data if I fat-finger
some SQL or code. However, I'll try opening a table prior to designing.

Hum, since you got a local copy, then the above might not help too much (try
disabling the track autoname correct). The other thing to check is your
default

I suppose I would put the "open" code in my startup menu form's OnLoad or
OnOpen event but not sure what code I would put as this isn't ODBC but
simple linked tables. Sorry if I'm obtuse!

Yes, you about got the above correct. In your startup code, you want to open
up a global reocrdset (table in the back end), or open a small form, and
minimize it (and even set visible = false). The effects on performance is
often dramatic (while this would be for the "users", and not design mode -
however, opening a table, and min it during development often makes a huge
difference also).

 

[SusanV]

I already removed the autoname track and subrecordset stuff. Interesting on
the global recordset, I'll have to look into this on Monday - I've got other
issues i have to resolve this afternoon. Gah, too much thinking today my
brain is tired!

Thanks again for your help, it's much appreciated!

Susan

 

[Tony Toews]

I already removed the autoname track and subrecordset stuff. Interesting on
the global recordset, I'll have to look into this on Monday - I've got other
issues i have to resolve this afternoon. Gah, too much thinking today my
brain is tired!

Yes, you have to have a persistent connection open especially when
working in design view to get good performance. The other things
you've already done help.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm