database bloat/size

[JohnLute]

I'm using Access 2003 and my FE is in 2000 format. I've been puzzling with
the file size of my secured FE. It's a project of 7 years in the making. It's
gradually grown in size however recently it's bloated to nearly 1GB. Of
course, I've compacted/repaired/compiled/decompiled and compacted it to
death. I've also imported all objects into a new file only to watch it bloat
after a few minor tweaks or re-designs.

I've recently imported all objects into a new file and it seems to be
hovering around 41MB. I thought I'd post the specifics here to see if anyone
has a similar design and/or experiences.

My FE links to 338 tables. Here is a count of all other objects:
Queries: 214
Forms: 447
Reports: 584
Modules: 5

Some people say that I've obviously done something "wrong" to have so many
objects and others have commented that they've seen bigger.

All I can say is that it is what it is for very good reasons which have been
poured over for the last 7 years. I'd just like to know if anyone out there
has had similar experiences with bloat as described here. How is it possible
for a file to explode to 1GB and then be imported into a new file around
40MB? What's wrong with Access?

It's frustrating to have to import all objects into a new file and then go
through the fuss of securing that new file every time I make a few changes
that result in it bloating to an unreasonable file size!

 

[Fred]

IMHO opinion, these nasty rips should be reserved for when he actually
solicits, which is pretty often, but not this time.

 

[Tony Toews [MVP]]

I'm using Access 2003 and my FE is in 2000 format. I've been puzzling with
the file size of my secured FE. It's a project of 7 years in the making. It's
gradually grown in size however recently it's bloated to nearly 1GB. Of
course, I've compacted/repaired/compiled/decompiled and compacted it to
death. I've also imported all objects into a new file only to watch it bloat
after a few minor tweaks or re-designs.

After doing a little work in a 40 Mb MDB with not quite as many forms
and reports as yours I frequently would see it increase by about 20
Mb. Then it seems to stay at that mark for a free days or a week
until I compact it again.

How much does it bloat after working with it for few hours and then a
few days.

I've recently imported all objects into a new file and it seems to be
hovering around 41MB. I thought I'd post the specifics here to see if anyone
has a similar design and/or experiences.

My FE links to 338 tables. Here is a count of all other objects:
Queries: 214
Forms: 447
Reports: 584
Modules: 5

41 Mb is a reasonable size for that many objects.

All I can say is that it is what it is for very good reasons which have been
poured over for the last 7 years. I'd just like to know if anyone out there
has had similar experiences with bloat as described here. How is it possible
for a file to explode to 1GB and then be imported into a new file around
40MB? What's wrong with Access?

Is this a repeating thing or did this just happen once or twice. This
might be caused by accidentally going into the FE MDB twice at the
same time.

It's frustrating to have to import all objects into a new file and then go
through the fuss of securing that new file every time I make a few changes
that result in it bloating to an unreasonable file size!

Never used Access user level security so I regretfully can't share
your pain. Can the security part be done in VBA?

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[JohnLute]

Thanks for the reply, Tony.

After doing a little work in a 40 Mb MDB with not quite as many forms
and reports as yours I frequently would see it increase by about 20
Mb. Then it seems to stay at that mark for a free days or a week
until I compact it again.

How much does it bloat after working with it for few hours and then a
few days.

I typically work on it every day anywhere from 1-4 hours. I compile and
compact/repair EVERY day.

41 Mb is a reasonable size for that many objects.

THAT is the question I was hoping to get some clarity on. Thanks!

Is this a repeating thing or did this just happen once or twice. This
might be caused by accidentally going into the FE MDB twice at the
same time.

It's been a repeating thing. If memory serves the first time it blew up it
had ballooned to about 150+MB within a couple hours. When I
compacted/repaired it went down but had gained some weight! When I went back
to work on a particularly complex query it almost instantly expanded again.
I've found that any time I work on that query I can expect it to balloon. It
never lets me down. Sometimes compacting gets it back but more often it
doesn't.

Never used Access user level security so I regretfully can't share
your pain. Can the security part be done in VBA?

That's a good suggestion. I'll need to explore it.

 

[Jeff Boyce]

John

Are you saying that EVERY time you work with QUERY X it blows up?

Any chance you'd be willing to kill QUERY X, compact/repair, then re-create
QUERY X? (sometimes Access gets subtly corrupted and it just seems to work
better to throw out what breaks and start over...)

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Access MVP


--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Tony Toews [MVP]]

Are you saying that EVERY time you work with QUERY X it blows up?

Any chance you'd be willing to kill QUERY X, compact/repair, then re-create
QUERY X? (sometimes Access gets subtly corrupted and it just seems to work
better to throw out what breaks and start over...)

I was thinking the same thing. But rather than recreate a complex
query I'd suggest going into SQL design view, copy and paste the SQL
into Notepad, delete the query and then copy it back from Notepad.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[JohnLute]

Are you saying that EVERY time you work with QUERY X it blows up?

Yes. In fact, right before I read your post I opened it in design mode and
didn't do ANYTHING to it except move/re-size the tables and queries. I saved
it and looked at my db size. It popped by 2MB!

Any chance you'd be willing to kill QUERY X, compact/repair, then re-create
QUERY X? (sometimes Access gets subtly corrupted and it just seems to work
better to throw out what breaks and start over...)

I never considered that but it might be worth the effort. Here's its SQL
just for your info:

SELECT tblPKProfilesAssociations.ProfilesAssociations AS PKWTID,
tblProfiles_1.Version AS PKWTV, tblProfiles_1.Description AS PKWTDesc,
tblProfiles_1.OriginDate AS PKWTOrDate, tblProfiles_1.Activity AS PKWTAct,
tblProfiles_1.ApprovedDate AS PKWTAppDate, tblProfiles_1.ActiveDate AS
PKWTActDate, tblProfiles_1.InactiveDate AS PKWTInDate, tblProfiles_1.Comments
AS PKWTCom, tblProfiles_1.Type AS PKWTCalcType,
tblFGPhysicalAttributes.txtProfileID, tblProfiles.Version,
tblProfiles.Description, tblProfiles.Status, tblProfiles.OriginDate,
tblProfiles.Activity, tblProfiles.Comments, tblFGPhysicalAttributes.Brand,
Trim([Claim] & " " & [FlavorStyle] & " " & [Category]) AS FGDesc,
tblFGPhysicalAttributes.UnitDescription, qryProfilesGroupsFG.GroupDesc,
tblFGPhysicalAttributes.ShippedIn, tblFGPhysicalAttributes.UnitCount,
tblFGPhysicalAttributes.SubUnitCount, tblFGPhysicalAttributes.UnitSize,
tblFGPhysicalAttributes.UnitUOM,
IIf([UnitUOM]="Ct.",+Nz([UnitCount],1)*Nz([SubUnitCount],1)*[UnitSize],Null)
AS UnitSizeCt,
IIf([UnitUOM]="Ct.",Null,+IIf([SubUnitCount],[SubUnitCount]*[UnitSize],[UnitSize]))
AS UnitSizeCtc, Nz([UnitSizeCt],[UnitSizeCtc]) AS UnitSizec,
IIf([UnitUOM]="Ct.",[UnitSizec],[UnitCount]) AS UnitCountc,
qryFGsINGsDensAllergens.LabDensitylbgal,
qryFGsINGsDensAllergens.CalcDensitylbgal,
IIf(IsNull([CalcDensitylbgal]),[LabDensitylbgal],[CalcDensitylbgal]) AS
DensityNz, qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfSUBUNPRODWtlb AS
SUBUNPRODWt, qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfSUBUNPRODWtlbTotal
AS SUBUNPRODWtTotal, qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfUNPRODWtlb
AS PRODWt, qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfUNPPRODWtlbTotal AS
PRODWtTotal, qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfSUBUNPKWtlbTotal AS
SUBUNPKWtTotal, qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfUNPKWtlb AS
UNPKWt, qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfUNPKWtlbTotal AS
UNPKWtTotal, Round([UnitSizec]*[lbConvFactor],6) AS UNNtWtConvlb,
Round([UnitSizec]*[galConvFactor],6) AS UNNtWtConvgal,
IIf(IsNull([SubUnitCount]),[UnitCount]*[PRODWt],[UnitCount]*[SUBUNPRODWtTotal])
AS UNPRODNtWt, Round(Nz([UNNtWtConvlb],[UNNtWtConvgal]*[DensityNz]),6) AS
UNNtWt, IIf([UOMType]="WT",Round([UNNtWt]*0.025+[UNNtWt],6)) AS UNEstNtWtw,
IIf([UOMType]="VOL",Round([UNNtWt],6)) AS UNEstNtWtv,
Round(Nz([UNEstNtWtw],[UNEstNtWtv]),6) AS UNEstNtWt,
IIf(Nz([SUBUNPKWtTotal],0)<>0,Round(Nz([UNEstNtWtw],[UNEstNtWtv])+[SUBUNPKWtTotal],6),Round(Nz([UNEstNtWtw],[UNEstNtWtv])+[SumOfSumOfUNPKWtlb],6))
AS UNEstGrWt, [UNEstGrWt]-[UNEstNtWt] AS UNPKNtWt, [UNPKNtWt]/[UNEstGrWt] AS
UNPKpercent, [UnitLoadLength]*[tblUOMLength].[inConvFactor] AS ULLinConv,
[UnitLoadWidth]*[tblUOMLength_1].[inConvFactor] AS ULWinConv,
[UnitLoadHeight]*[tblUOMLength_2].[inConvFactor] AS ULHinConv,
IIf([ULLinConv]>=48,[ULLinConv],[PTL]) AS ULL,
IIf([ULWinConv]>=40,[ULWinConv],[PTW]) AS ULW, [ULHinConv]*[ULL]*[ULW] AS
ULcuin, [ULcuin]/1728 AS ULcuft, qryPKWTCalcsPTDims.PTL,
qryPKWTCalcsPTDims.PTW, qryPKWTCalcsPTDims.PTH,
qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfCSPKWtlb,
qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfCSPKWtlbTotal,
qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfPTPKWtlb,
qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfPTPKWtlbTotal,
tblFGPhysicalAttributes.UnitLoadStack, tblFGPhysicalAttributes.Ti,
tblFGPhysicalAttributes.Hi, tblProfilesStorage.StorageTime,
tblProfilesStorage.STIMEUOM, tblProfilesStorage.StorageCondition,
tblFGPhysicalAttributes.AdditionalUnits,
qryFGsINGsDensAllergens.ProfilesAssociations AS FAING,
qryFGProcessingDrumNumbers.Drums, qryFGsINGsDensAllergens.Allergens
FROM ((((((((((((tblFGPhysicalAttributes INNER JOIN tblUOMLength ON
tblFGPhysicalAttributes.ULLUOM = tblUOMLength.txtUOMLength) INNER JOIN
tblUOMLength AS tblUOMLength_1 ON tblFGPhysicalAttributes.ULWUOM =
tblUOMLength_1.txtUOMLength) INNER JOIN tblUOMLength AS tblUOMLength_2 ON
tblFGPhysicalAttributes.ULHUOM = tblUOMLength_2.txtUOMLength) INNER JOIN
qryPKWTCalcsPTDims ON tblFGPhysicalAttributes.txtProfileID =
qryPKWTCalcsPTDims.txtProfileID) LEFT JOIN qryPKProfilesAssociationsPKWTsFGs
ON tblFGPhysicalAttributes.txtProfileID =
qryPKProfilesAssociationsPKWTsFGs.txtProfileID) LEFT JOIN
qryFGsINGsDensAllergens ON tblFGPhysicalAttributes.txtProfileID =
qryFGsINGsDensAllergens.txtProfileID) LEFT JOIN tblUOMVolumeLiquidMass ON
tblFGPhysicalAttributes.UnitUOM = tblUOMVolumeLiquidMass.txtUOMVolumeLiquid)
LEFT JOIN qryProfilesGroupsFG ON tblFGPhysicalAttributes.txtProfileID =
qryProfilesGroupsFG.txtProfileID) INNER JOIN tblProfiles ON
tblFGPhysicalAttributes.txtProfileID = tblProfiles.txtProfileID) LEFT JOIN
qryFGProcessingDrumNumbers ON tblFGPhysicalAttributes.txtProfileID =
qryFGProcessingDrumNumbers.txtProfileID) LEFT JOIN tblProfilesStorage ON
tblFGPhysicalAttributes.txtProfileID = tblProfilesStorage.txtProfileID) INNER
JOIN tblPKProfilesAssociations ON tblFGPhysicalAttributes.txtProfileID =
tblPKProfilesAssociations.txtProfileID) INNER JOIN tblProfiles AS
tblProfiles_1 ON tblPKProfilesAssociations.ProfilesAssociations =
tblProfiles_1.txtProfileID
WHERE (((tblProfiles_1.Type)="PKCALC"));

JOPO (just one person's opinion)

Opinions are why I post here!

Thanks, Jeff!

 

[johnlute]

Hi, Jeff.

I wanted to get back with you on this. I re-built the query from
scratch and then accidentally deleted it while testing it against the
original - UGH. I came back here to review Tony's suggestion of
copying/pasting the SQL into Notepad and gave it a whirl. I'll have to
work with it a bit and let you know.

Since yesterday I've done a little work on a few forms and a couple
queries including the one here. I've not compacted since making these
changes and the db is open right now and has a 48MB file size. OK - I
just compiled and compacted and the file size is now 45MB. This is up
4MB since my first post!

I guess I'll just monitor how it grows since the "problem" query has
been addressed.

One thing about that query that I hadn't considered is that I had a
lot of formatting in the field properties of many of the columns. I
did this during the development of the query but ultimately, I don't
need any of it now. Of course, copying/pasting it back and forth from
Notepad has eliminated all of the formatting. Maybe this is what was
generating bloat...?

 

[Jeff Boyce]

John

I don't have any experience with whether formatting query output bloats the
db ... ?feel like experimenting in your "copious free time"? <g>

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hi, Jeff.

I wanted to get back with you on this. I re-built the query from
scratch and then accidentally deleted it while testing it against the
original - UGH. I came back here to review Tony's suggestion of
copying/pasting the SQL into Notepad and gave it a whirl. I'll have to
work with it a bit and let you know.

Since yesterday I've done a little work on a few forms and a couple
queries including the one here. I've not compacted since making these
changes and the db is open right now and has a 48MB file size. OK - I
just compiled and compacted and the file size is now 45MB. This is up
4MB since my first post!

I guess I'll just monitor how it grows since the "problem" query has
been addressed.

One thing about that query that I hadn't considered is that I had a
lot of formatting in the field properties of many of the columns. I
did this during the development of the query but ultimately, I don't
need any of it now. Of course, copying/pasting it back and forth from
Notepad has eliminated all of the formatting. Maybe this is what was
generating bloat...?