lightweight objects

[ngan]

I have a mde file that starts off around 2MB. I noticed after a few days,
certain people's mde copy bloated to over 4MB. I'm trying to figure out why
this is and how to minimize the bloating.

I don't have any macros in my db files. I use all modules or functions,
even with the simplest code like opening a form. I do close out all
recordsets. I read a KB article that gave suggestions on using lightweight
objects for simple coding:

http://support.microsoft.com/default.aspx?scid=kb;en-us;324589

Should I go back and try to make certain codes public functions or macros
and turn off the Has Module option, where applicable?

Would doing these things help keep the mde file from growing so much?

 

[Rick Brandt]

I have a mde file that starts off around 2MB. I noticed after a few
days, certain people's mde copy bloated to over 4MB. I'm trying to
figure out why this is and how to minimize the bloating.

I don't have any macros in my db files. I use all modules or
functions, even with the simplest code like opening a form. I do
close out all recordsets. I read a KB article that gave suggestions
on using lightweight objects for simple coding:

http://support.microsoft.com/default.aspx?scid=kb;en-us;324589

Should I go back and try to make certain codes public functions or
macros and turn off the Has Module option, where applicable?

Would doing these things help keep the mde file from growing so much?

Don't worry about it. 4 MB is still VERY small for an Access app. And it is
not a given that the growth will continue. Likely it will grow to a certain
size and then stabilize there.

 

[Klatuu]

Lightweight objects are a good concept, but not really practical. That
methodolgy is like wearing a girdle - the fat is still there, it has just
been moved around. It is normal for some growth to occur in an Access
application. An easy way to control it is to set Compact On Close to yes
(Tools->Options, General Tab).

 

[ngan]

Thanks for the input. If I set the compact to close, will it compact every
db or just the one I set?

Anyways, I've seen one user's copy grow to over 11MB! The tables are linked
to SQL so I'm trying to figure out what is making the FE grow so much.

anything I can do to figure that out?

 

[Klatuu]

Just the db you have the option set in. Now, here is a trick that not
everyone knows. You can set the Compact On Close in your back end database
and it will work there as well. What happens is that when the first user
opens his front end, it also opens the back end. The back end stays open
until the last user closes his front end, then the back end closes and the
compact and repair executes. This is a good thing.

 

[ngan]

Not sure if that will work with the BE because the BE is a SQL server. Plus,
the BE is in use practically 24/7.

Do you know how I can figure out why a user's FE would grow to over 11MB
when the original FE size is 2MB?

 

[Klatuu]

You are correct. This is only for mdb back ends.

 

[ngan]

thanks. For my other question: Do you know what I can do to figure out why
the user's FE grew to 11MB when the original size was 2MB? She's the only
one that has the FE grow that big. The FE file of other users who do the
same type of work doesn't grow that much.

Ngan

 

[Klatuu]

She does more work than the others?
I don't really know for sure. I would look to see what functionality she
uses compared to the other users. If she is using a particular object more
than anyone else, it might the be suspect.

 

[Marshall Barton]

Do you know how I can figure out why a user's FE would grow to over 11MB
when the original FE size is 2MB?

Check your VBA procedures for any code that creates new
objects (e.g. temp tables, CreateQueryDef, CreateReport,
etc.).

A related issue is saving existing objects. For example,
some people inadvertantly use DoCmd.Close , , acSaveYes
thinking they're saving the data record, when it really
saves the form's design. Actually, Access even saves some
stuff in the form even without using acSaveYes, so it's a
good idea to always use:
DoCmd.Close acForm, Me.Name, acSaveNo

OTOH, Accesss does need some space for normal operations
such as large recordsets for combo/list box's row source and
form/report's record source, so I recommend letting the
worst case user go for a month and tracking the growth in
that one front end mdb to see if it stabalizes at a
reasnable(?) size.

 

[ngan]

thanks for your suggestion, I'll try it and see how it goes after a couple of
weeks.

From memory, I don't think I have any VBA that creates new objects.
Most of my save record commands are:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
When closing a form, I just have docmd.close

Ngan