Fundamental MDB bloat issue

[Mike Oliver]

I'm trying to track down a problem we're having with our MDB growing
in size. We have a Windows service that is accessing a MDB constantly.
Part of what this service does is "refresh" the contents of a table by
deleting all records and then inserting into it. This is a process
that happens periodically over the course of a day.

It seems that the simple process of deleting and inserting records in
a table where the overall number of records _does not_ change causes
the MDB to grow over time in a linear fashion. Is this true?

Is it a true statement that Jet/Access will not free up the space used
for these deleted records? If so, that means that the only way to
address this MDB bloat issue is to periodically compact the MDB
programmatically, right?

Thanks,

Mike Oliver

[Rick Brandt]

"Mike Oliver" <mi**************@yahoo.com> wrote in message
news:38**************************@posting.google.c om...

I'm trying to track down a problem we're having with our MDB growing
in size. We have a Windows service that is accessing a MDB constantly.
Part of what this service does is "refresh" the contents of a table by
deleting all records and then inserting into it. This is a process
that happens periodically over the course of a day.

It seems that the simple process of deleting and inserting records in
a table where the overall number of records _does not_ change causes
the MDB to grow over time in a linear fashion. Is this true?
Yep.
Is it a true statement that Jet/Access will not free up the space used
for these deleted records? If so, that means that the only way to
address this MDB bloat issue is to periodically compact the MDB
programmatically, right?

Yep.

Is there a way you could do the refresh without the "delete all and then
replace" approach? If not you could locate this table in an external MDB
that contains nothing but this table and the refresh routine could
completely delete and recreate the file thus avoiding the bloat issue.
That might mess up relationships to the table though.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com