Memory Usage Out of Control

[Guest]

I have a database in Access 2000 format, built with Access 2002. The DB is
54MB when compacted. I am building a form for data entry that pulls from a
query that has about 230 fields, which itself pulls from about 30 different
tables. The form itself has an area at the top with basic information about
individual projects with several tabs below that contain specific
characteristics about different areas of the project. In all, there are
about 230 fields on the form. My problem comes when trying to modify and
save the form. If I open the form in Design View, make even a single change,
and try to save the Form it take up to 7-8 minutes for the save to complete.
During this time, I watch the memory usage in the Task Manager and the memory
used for Access usually climbs north of 150,000K. Lately it has been around
175,000K.

Is this normal and is there anything I can do to speed the save function?

Thanks in advance.

 

[Dirk Goldgar]

I have a database in Access 2000 format, built with Access 2002. The
DB is 54MB when compacted. I am building a form for data entry that
pulls from a query that has about 230 fields, which itself pulls from
about 30 different tables. The form itself has an area at the top
with basic information about individual projects with several tabs
below that contain specific characteristics about different areas of
the project. In all, there are about 230 fields on the form.

Holy smokes! You may want to reorganize that into simpler main form
with multiple subforms.

My
problem comes when trying to modify and save the form. If I open the
form in Design View, make even a single change, and try to save the
Form it take up to 7-8 minutes for the save to complete. During this
time, I watch the memory usage in the Task Manager and the memory
used for Access usually climbs north of 150,000K. Lately it has been
around 175,000K.

Is this normal and is there anything I can do to speed the save
function?

That could well be due to the operation of the Name Autocorrect
misfeature. Click Tools -> Options..., General, and uncheck it.

 

[Guest]

Dirk,
Thanks for the response. That seems to take care of it. Fired up the form,
made a few changes and it saved in about 2 seconds instead of 7-8 minutes.

As for re-designing the form, I am planning on doing that soon. We will be
moving the back-end to SQL Server in the next few months. Once that happens
I will re-work the front end to work a little smoother until we get a custom
interface built. Wasn't comfortable with sub-forms when I started building
this, but now I think I can do it.

Thanks again.

 

[Tony Toews]

Wasn't comfortable with sub-forms when I started building
this, but now I think I can do it.

Also consider using the tab control.

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

 

[Guest]

While researching how to speed up front ends, I ran across a suggestion to
have a form that maintains open links to all the tables you frequently
access. It was supposed to speed things up because access does not have to
recreate the idb file all the time. The problem was this created a huge
memory leak and tied up 100% of the processor all the time, and I was only
pulling 1 field from 5 tables. I can't imagine what would happen with more.
I had to stop using the form.