database super bloat

[Dave]

finally got a copy of an mdb from one of my users. this app is a split
database made with access xp and deployed with the packaging wizard and
runtime. the user with a problem is running win 98, and has access 97.
what happens is after he adds some data to the backend it grows from a
normal 800k up to 34MB! when i get it here i can compact it and it goes
right back to 800kb, surprisingly quickly i might add... any ideas of what
i could look for that might cause this??

 

[Steve Schapel]

Dave,

Are you using backend tables to temporarily hold data, so there's a lot
of appending/deleting, or make-table queries, or such like?

 

[Dave]

no make table queries at all. the operation when this seems to happen is
when i use a vba process that first steps through a table and deletes each
record then reads a text file and inserts new records one at a time. there
are normally about as many records added as were deleted, but with all new
index values. in normal operation this inflates the backend file about
100%, which i expect... however on this one machine it blows up 40x. it
does seem to be recoverable with a compact, but it shouldn't be happening in
the first place and could cause confusion and problems for some of my users
with older machines with limited disk space.

 

[Albert D. Kallal]

You do realize that when you delete a record, that spaces is NOT recovered
until you compact.

Thus,a as a general rule you want to avoid code that un-necessary deletes
data.

However, if the same bloat issue does not occur when you run the SAME code
and the SAME data on another pc, then I would suggest that you install the
service packs, and also the updates to jet, as a number of bugs, and serious
bloat problems are fixed by doing this...


so, to run things, you MUST install:

sr1
sr2b

and jet35sp3.exe

 

[Tony Toews]

no make table queries at all. the operation when this seems to happen is
when i use a vba process that first steps through a table and deletes each
record then reads a text file and inserts new records one at a time. there
are normally about as many records added as were deleted, but with all new
index values. in normal operation this inflates the backend file about
100%, which i expect... however on this one machine it blows up 40x. it
does seem to be recoverable with a compact, but it shouldn't be happening in
the first place and could cause confusion and problems for some of my users
with older machines with limited disk space.

I wonder if they are current on the Jet 3.5 SPs? Jet 3.5 SP 3 -
msjet35.dll - 3.51.3328.0.

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