help, my accessxp-db certenly become "compressed"

[erik]

Hi
While working with my AccessXP-db, just tasting in new records, something happend
"data my bee lost"
but Access started up again and I kept working.

Later when making a backupcopy (used to be 14Mb) it was now only 4Mb ! ! !

When looking into explorer there is now 2 files. A backup at 14Mb and one on 4Mb and
it seems to be the 4Mb-file that I have been working on all night and at a fast look
no data seems to be missing ))

What has happend ???

 

[Nikos Yannacopoulos]

Erik,

One of Access's shortcomings (actually Jet's, which is Access's database
engine) is that it does not release space when no longer used, like when
you delete records or objects. This causes the database to bloat over
time, and this is why Access has a built-in compacting function -
actually it is Compact and Repair, it recovers unused space and repairs
indices etc. The point is, you should do it once in a while (Tools >
Database Utilities > Compact and Repair Database), and following that
you will witness the kind of file size reduction you are talking about.
A good idea is to set the database to auto-compact every time you close
it (Tools > Options, General tab, Compact On Close).
Now, the "data my be lost" message sounds like you changed a field's
type or size in table design. In that case you do lose data if, for
instance, you change a text fields length from 255 to 20 and you have
data with length > 20 (it's truncated to 20 chars), but if your data
wasn't any longer than 20 in the first place, then you lose nothing.
This is probably why you don't see any change in your data. The
secondary effect of such a change is a database size reduction
(following a compact and repair), as a field which required 255 bytes of
storage, now only requires 20 in the above example; of course, this only
becomes apparent on a big table: (255 - 20) = 235 butes x 100,000
records = 22MB, while 235 bytes x 50 records = 12KB.

Hope it is clearer now.
Nikos