database bloat/size

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!

--
www.Marzetti.com

Here are a few things that can cause rapid bloat .......
1. Tweaks or re-designs. They might not be as minor as you think.
Compacting after the tweaks or re-designs are done should take care of it.
2. Storing image files in the database. This has been discussed ad
infinitum in the forums.
3. Repeated deleting of records in one or more tables then entering or
appending new records to the table(s).

41 MB seems reasonable for the number of objects in your DB.

Steve



"JohnLute" wrote in message
...
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!

--
www.Marzetti.com

"Steve" schreef in bericht
...
Here are a few things that can cause rapid bloat .......
1. Tweaks or re-designs. They might not be as minor as you think. Compacting after
the tweaks or re-designs are done should take care of it.
2. Storing image files in the database. This has been discussed ad infinitum in the
forums.
3. Repeated deleting of records in one or more tables then entering or appending new
records to the table(s).

41 MB seems reasonable for the number of objects in your DB.

Steve



--
Get lost $teve. Go away... far away....
No-one wants you here... no-one needs you here...

OP look at http://home.tiscali.nl/arracom/whoissteve.html
(Website has been updated and has a new 'look'... we have passed 10.000 pageloads... it's
a shame !!)

For those who don't 'agree' with this mail , because $teve was 'helpfull' with his post...
We warned him a thousand times... Sad, but he is not willing to stop advertising...

He is just toying with these groups... advertising like hell... on and on... for years...
oh yes... and sometimes he answers questions... indeed...
and sometimes good souls here give him credit for that...

== We are totally 'finished' with $teve now...
== Killfile 'StopThisAdvertising' and you won't see these mails....

Arno R

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

JohnLute wrote:

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/

Thanks for the reply, Tony.

"Tony Toews [MVP]" wrote:

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.

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.

"JohnLute" wrote in message
...
Thanks for the reply, Tony.

"Tony Toews [MVP]" wrote:

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" wrote:

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/

"Jeff Boyce" wrote:

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.SumOfSumOfSUBUNP RODWtlb AS
SUBUNPRODWt, qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfSUBUNP RODWtlbTotal
AS SUBUNPRODWtTotal, qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfUNPROD Wtlb
AS PRODWt, qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfUNPPRO DWtlbTotal AS
PRODWtTotal, qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfSUBUNP KWtlbTotal AS
SUBUNPKWtTotal, qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfUNPKWt lb AS
UNPKWt, qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfUNPKWt lbTotal 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.SumOfSumOfCSPKWt lb,
qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfCSPKWt lbTotal,
qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfPTPKWt lb,
qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfPTPKWt lbTotal,
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!

I agree, and I have said so, but StopThisAdvertising seems to have announced
that the fact Steve is posting at all is grounds for attack. People may
start to see the attacks as a personal vendetta when they are in response to
non-solicitation postings..

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200910/1