Control over DB size

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

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

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

Senthil,

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

Nikos

"Senthil" wrote in message
...
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" wrote in message
...
Senthil,

Deleting records (among other things) leaves behind unused space in an
.mdb
file. Unfortunately, Access is not very good at releasing unused
pace -
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" wrote in message
...
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