Control over DB size

[Senthil]

I had a Access DB with 2 tables. After creation the size of DB was 400 KB.
To this DB records were addded & removed frequently. Curently there is only
one record in both tables in the DB. The size of the DB kept on increasing &
it is now 1.9 MB. Ideally the size of DB should be few KB more or less than
the initial size. Is there any settings that allow to control the DB size?
Can anybody explain what happens inside & why DB behaves like this?

Thanks & regards,
senthil

 

[Nikos Yannacopoulos]

Senthil,

Deleting records (among other things) leaves behind unused space in an .mdb
file. Unfortunately, Access is not very good at releasing unused space -
actually it doesn't on its own, which is what causes databases to bloat over
time. This can be dealt with by regular compaction (Tools > Database
Utilities > Compact and Repair database).
If you have Access 2000 or later, you can set it to auto-compact on exit
(Tools > Options, General tab, Compact on Close - this setting is .mdb
specific, not global). This will save you a lot of frustration on a single
user database, as well as on the FE of a split multiuser one. The BE of a
multiuser database is a different story though, this technique won't work
there. In that case, the best practice IMHO is a few lines of code in
another .mdb or an .exe to compact the database, scheduled to run nightly
(an open database cannot be compacted).

HTH,
Nikos

 

[Senthil]

Hi Nikos,

Thanks it worked. I have another doubt. Will compact option work even when i
open and close the mdb programmatically?

Thanks & Regards,

Senthil

 

[Nikos Yannacopoulos]

Senthil,

I think it will. Give it a shot and verify!

Nikos