Front-end bloat

One of my larger Access97 systems is now 12mb of code: queries, forms,
reports, and modules (with a few tiny lookup tables). It also contains 45
links to an Access back-end, and 20 links to a SQL Server db

Just today I replaced 16 subforms used for looking up data with 1 subform
and a little code (ala David Fenton's LookupAdmin system). That helped a
little, but it's still a sizeable program.

Which objects take up the most space? Is there a way to see the individual
object sizes?

re: Front-end bloat

Did you compact that front-end after deleting the 16 original subforms? If
you did not, the space hasn't been retrieved. Another source of bloat is
temporary tables (you'll find a method for avoiding that by using a
temporary database to contain the temporary tables, at MVP Tony Toews' site,
http://www.granite.ab.ca/accsmstr.htm), and yet another is internal
temporary workspace generated by various actions in Access (one of which is
the running of certain queries).

I doubt that information on which objects take up the most space would be
helpful -- in any case, that information isn't available. Obviously, tables
with lots of records can be the largest objects around, but that should not
be the case in your front-end (unless you are using temporary tables in the
front-end database).

Larry Linson
Microsoft Access MVP


"DFS" <nospam@nospam.com> wrote in message
news:1020ck511kud667@corp.supernews.com...[color=blue]
> One of my larger Access97 systems is now 12mb of code: queries, forms,
> reports, and modules (with a few tiny lookup tables). It also contains 45
> links to an Access back-end, and 20 links to a SQL Server db
>
> Just today I replaced 16 subforms used for looking up data with 1 subform
> and a little code (ala David Fenton's LookupAdmin system). That helped a
> little, but it's still a sizeable program.
>
> Which objects take up the most space? Is there a way to see the[/color]
individual[color=blue]
> object sizes?
>
>[/color]

re: Front-end bloat

"DFS" <nospam@nospam.com> wrote:
[color=blue]
>One of my larger Access97 systems is now 12mb of code: queries, forms,
>reports, and modules (with a few tiny lookup tables).[/color]

When's the last time you did a decompile? See Decompile or how to reduce Microsoft
Access MDB/MDE size and decrease start-up times
http://www.granite.ab.ca/access/decompile.htm

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

re: Front-end bloat

"DFS" <nospam@nospam.com> wrote in
news:1020ck511kud667@corp.supernews.com:
[color=blue]
> One of my larger Access97 systems is now 12mb of code: queries,
> forms, reports, and modules (with a few tiny lookup tables). It
> also contains 45 links to an Access back-end, and 20 links to a
> SQL Server db
>
> Just today I replaced 16 subforms used for looking up data with 1
> subform and a little code (ala David Fenton's LookupAdmin system).
> That helped a little, but it's still a sizeable program.
>
> Which objects take up the most space? Is there a way to see the
> individual object sizes?[/color]

Are there any graphics in it? I have seen that bloat things a lot.

I'd recommend that you try creating a brand-new MDB and importing,
and then see how big it is with the objects freshly imported (do a
compact but don't open anything). If it's 8 or 10MBs, then 12 sounds
about right for one that's in use. If it's 4 or 5, then there must
be something wrong with the MDB file (I've seen corruption that is
never removed by a compact that bloats the file significantly, but
that doesn't cause any noticeable problems).

If this new MDB then bloats up, then you've got a design problem.
You shouldn't be writing to any tables in the front end, especially
if you're deleting records from those tables, and you shouldn't be
writing temporary QueryDefs. The jury is out on unnamed using
QueryDefs to open recordsets, which some people say can lead to
bloat and others claim cannot.

Another thing is to convert to an MDE and see what size the MDE
comes out as.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

re: Front-end bloat

"Larry Linson" <bouncer@localhost.not> wrote in message
news:WyWTb.292$CE5.117@nwrddc03.gnilink.net...[color=blue]
> Did you compact that front-end after deleting the 16 original subforms? If
> you did not, the space hasn't been retrieved. Another source of bloat is
> temporary tables (you'll find a method for avoiding that by using a
> temporary database to contain the temporary tables, at MVP Tony Toews'[/color]
site,[color=blue]
> http://www.granite.ab.ca/accsmstr.htm), and yet another is internal
> temporary workspace generated by various actions in Access (one of which[/color]
is[color=blue]
> the running of certain queries).[/color]

Sure, I did all that. Emptied my temp tables, compacted, etc. It's 12mb
after doing that. I'm not really worried about the size , just curious.

Thanks


[color=blue]
> I doubt that information on which objects take up the most space would be
> helpful -- in any case, that information isn't available. Obviously,[/color]
tables[color=blue]
> with lots of records can be the largest objects around, but that should[/color]
not[color=blue]
> be the case in your front-end (unless you are using temporary tables in[/color]
the[color=blue]
> front-end database).
>
> Larry Linson
> Microsoft Access MVP
>
>
> "DFS" <nospam@nospam.com> wrote in message
> news:1020ck511kud667@corp.supernews.com...[color=green]
> > One of my larger Access97 systems is now 12mb of code: queries, forms,
> > reports, and modules (with a few tiny lookup tables). It also contains[/color][/color]
45[color=blue][color=green]
> > links to an Access back-end, and 20 links to a SQL Server db
> >
> > Just today I replaced 16 subforms used for looking up data with 1[/color][/color]
subform[color=blue][color=green]
> > and a little code (ala David Fenton's LookupAdmin system). That helped[/color][/color]
a[color=blue][color=green]
> > little, but it's still a sizeable program.
> >
> > Which objects take up the most space? Is there a way to see the[/color]
> individual[color=green]
> > object sizes?
> >
> >[/color]
>
>[/color]

re: Front-end bloat

"David W. Fenton" <dXXXfenton@bway.net.invalid> wrote in message
news:Xns9484D898BF841dfentonbwaynetinvali@24.168.1 28.74...[color=blue]
> "DFS" <nospam@nospam.com> wrote in
> news:1020ck511kud667@corp.supernews.com:
>[color=green]
> > One of my larger Access97 systems is now 12mb of code: queries,
> > forms, reports, and modules (with a few tiny lookup tables). It
> > also contains 45 links to an Access back-end, and 20 links to a
> > SQL Server db
> >
> > Just today I replaced 16 subforms used for looking up data with 1
> > subform and a little code (ala David Fenton's LookupAdmin system).
> > That helped a little, but it's still a sizeable program.
> >
> > Which objects take up the most space? Is there a way to see the
> > individual object sizes?[/color]
>
> Are there any graphics in it? I have seen that bloat things a lot.[/color]

No graphics. Lots of forms:

91 forms (2/3 are subforms)
25 ODBC links
20 local tables (all small)
46 Access links
6 modules
106 queries
47 reports (about half are subreports)

It's getting pretty big. I have another system (Access2000) that's even
bigger; it's approaching 20mb.


[color=blue]
> I'd recommend that you try creating a brand-new MDB and importing,
> and then see how big it is with the objects freshly imported (do a
> compact but don't open anything). If it's 8 or 10MBs, then 12 sounds
> about right for one that's in use. If it's 4 or 5, then there must
> be something wrong with the MDB file (I've seen corruption that is
> never removed by a compact that bloats the file significantly, but
> that doesn't cause any noticeable problems).[/color]

Haven't tried a new .mdb. I'm not really worried about the size, just
curious.


[color=blue]
> If this new MDB then bloats up, then you've got a design problem.[/color]
[color=blue]
> You shouldn't be writing to any tables in the front end, especially
> if you're deleting records from those tables,[/color]

I disagree.

[color=blue]
> and you shouldn't be
> writing temporary QueryDefs. The jury is out on unnamed using
> QueryDefs to open recordsets, which some people say can lead to
> bloat and others claim cannot.[/color]

Don't use them.

[color=blue]
> Another thing is to convert to an MDE and see what size the MDE
> comes out as.[/color]

I always distribute the .mde, and it's about 6mb.


Thanks


[color=blue]
> --
> David W. Fenton http://www.bway.net/~dfenton
> dfenton at bway dot net http://www.bway.net/~dfassoc[/color]

re: Front-end bloat

"DFS" <nospam@nospam.com> wrote in
news:1020sjeab9luc9a@corp.supernews.com:
[color=blue]
> "David W. Fenton" <dXXXfenton@bway.net.invalid> wrote in message
> news:Xns9484D898BF841dfentonbwaynetinvali@24.168.1 28.74...[/color]
[color=blue][color=green]
>> Another thing is to convert to an MDE and see what size the MDE
>> comes out as.[/color]
>
> I always distribute the .mde, and it's about 6mb.[/color]

You mean the source MDB is 12MBs and the resulting MDB is 6MBs?

Wow! That's a huge difference.

Do you have conditional compiling turned off?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

re: Front-end bloat

"David W. Fenton" <dXXXfenton@bway.net.invalid> wrote in message
news:Xns9485780177A57dfentonbwaynetinvali@24.168.1 28.86...[color=blue]
> "DFS" <nospam@nospam.com> wrote in
> news:1020sjeab9luc9a@corp.supernews.com:
>[color=green]
> > "David W. Fenton" <dXXXfenton@bway.net.invalid> wrote in message
> > news:Xns9484D898BF841dfentonbwaynetinvali@24.168.1 28.74...[/color]
>[color=green][color=darkred]
> >> Another thing is to convert to an MDE and see what size the MDE
> >> comes out as.[/color]
> >
> > I always distribute the .mde, and it's about 6mb.[/color]
>
> You mean the source MDB is 12MBs and the resulting MDB is 6MBs?[/color]

The source .mdb is 12mb, and the resulting .mde is 6mb.


[color=blue]
> Wow! That's a huge difference.[/color]

I thought so too, but I haven't ever had any .mde problems.


[color=blue]
> Do you have conditional compiling turned off?[/color]

No. I'll look into it.


[color=blue]
> --
> David W. Fenton http://www.bway.net/~dfenton
> dfenton at bway dot net http://www.bway.net/~dfassoc[/color]

re: Front-end bloat

"DFS" <nospam@nospam.com> wrote in
news:1022ng5f70qj30@corp.supernews.com:
[color=blue]
> "David W. Fenton" <dXXXfenton@bway.net.invalid> wrote in message
> news:Xns9485780177A57dfentonbwaynetinvali@24.168.1 28.86...[color=green]
>> "DFS" <nospam@nospam.com> wrote in
>> news:1020sjeab9luc9a@corp.supernews.com:
>>[color=darkred]
>> > "David W. Fenton" <dXXXfenton@bway.net.invalid> wrote in
>> > message
>> > news:Xns9484D898BF841dfentonbwaynetinvali@24.168.1 28.74...[/color]
>>[color=darkred]
>> >> Another thing is to convert to an MDE and see what size the
>> >> MDE comes out as.
>> >
>> > I always distribute the .mde, and it's about 6mb.[/color]
>>
>> You mean the source MDB is 12MBs and the resulting MDB is 6MBs?[/color]
>
> The source .mdb is 12mb, and the resulting .mde is 6mb.[/color]

That's a huge difference. Code just doesn't take up that much space,
even in a really code-heavy MDB, so there must be a bunch of
temporary structures being discarded.
[color=blue][color=green]
>> Wow! That's a huge difference.[/color]
>
> I thought so too, but I haven't ever had any .mde problems.[/color]

Well, no MDE conversion problems at least indicates that you're not
carrying the obvious kinds of code corruption.
[color=blue][color=green]
>> Do you have conditional compiling turned off?[/color]
>
> No. I'll look into it.[/color]

You might want to review MichKa's article on compilation states:

The Real Deal on the /DECOMPILE Switch
http://trigeminal.com/usenet/usenet004.asp

Now I'm wondering if you're editing/deleting data in the front end
tables you have alluded to. That will cause bloat.

Of course, if I'm remembering correctly, the bloat doesn't all go
away with a compact?

Very strange!

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc