By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,518 Members | 886 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,518 IT Pros & Developers. It's quick & easy.

Access DB continues to grow in size

P: n/a
I have a very simple Access database containing a single table. It is
part of a data-logger were I INSERT a new record once every ten seconds.

Three times a day I DELETE records older than one week old. The number
of records remains about the same, but the physical size of the mdb file
on disk continues to grow. It is now over 44 MB.

If I use the Access GUI I can 'Compact and Repair database' the mdb file
from the tools menu and reduce the size to 2.5 MB.

Is there a way to compress this mdb file without using the Access GUI?
Is there an SQL command that will do it? I don't want to pay the price
of msaccess.exe just to perform this maintenance task.

Thanks.

Lynn
Nov 13 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Br
Lynn <Fo************@comcast.net> wrote:
I have a very simple Access database containing a single table. It is
part of a data-logger were I INSERT a new record once every ten
seconds.
Three times a day I DELETE records older than one week old. The
number of records remains about the same, but the physical size of
the mdb file on disk continues to grow. It is now over 44 MB.

If I use the Access GUI I can 'Compact and Repair database' the mdb
file from the tools menu and reduce the size to 2.5 MB.

Is there a way to compress this mdb file without using the Access GUI?
Is there an SQL command that will do it? I don't want to pay the
price of msaccess.exe just to perform this maintenance task.

Thanks.

Lynn


Either by code or setting the option to compact the database when it is
closed.

Access allocates more space for new data but does not release it (hence
you need to compact it).

--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response
Nov 13 '05 #2

P: n/a
Of course, the rest of that answer is that if you're just going to
expand the database by adding new records to it, what's the point of
the compact? Access _will_ reuse unused space.

Nov 13 '05 #3

P: n/a
Chuck Grimsby wrote:
Of course, the rest of that answer is that if you're just going to
expand the database by adding new records to it, what's the point of
the compact? Access _will_ reuse unused space.


allegedly :-)

--
[OO=00=OO]
Nov 13 '05 #4

P: n/a
"Chuck Grimsby" <c.*******@worldnet.att.net> wrote in
news:11**********************@g47g2000cwa.googlegr oups.com:
Of course, the rest of that answer is that if you're just going to
expand the database by adding new records to it, what's the point
of the compact? Access _will_ reuse unused space.


Well, it doesn't use it efficiently. After a certain amount of time,
fragmentation of index pages can lead to significant performance
degradation.

Indeed, other than just plain old preventative maintenance, I think
that re-writing index pages for the performance gain is probably the
most important thing that regular compacting accomplishes for you.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #5

P: n/a
I am doing a DELETE several times a day of all records older than a
specific date. I am never using this extra space. It grows and grows
and....

Also, I should have mentioned that I am manipulating the database via
Java and the jdbc driver. I suppose I could close the database and do a
system call to launch a VB application that does the compression and
then reopen the database after it is finished.

I was hoping for something like the 'vacuum' command in Postgress.

Any other ideas?

thanks.
lynn

David W. Fenton wrote:
"Chuck Grimsby" <c.*******@worldnet.att.net> wrote in
news:11**********************@g47g2000cwa.googlegr oups.com:

Of course, the rest of that answer is that if you're just going to
expand the database by adding new records to it, what's the point
of the compact? Access _will_ reuse unused space.

Well, it doesn't use it efficiently. After a certain amount of time,
fragmentation of index pages can lead to significant performance
degradation.

Indeed, other than just plain old preventative maintenance, I think
that re-writing index pages for the performance gain is probably the
most important thing that regular compacting accomplishes for you.

Nov 13 '05 #6

P: n/a
On Wed, 01 Jun 2005 19:02:19 -0600, Lynn <Fo************@comcast.net>
wrote:

The system call would be:
<path_to>msaccess.exe <path_to>my.mdb /repair /compact
You can run this command using the Windows scheduler, at a time where
nobody is in the db.

-Tom.
I am doing a DELETE several times a day of all records older than a
specific date. I am never using this extra space. It grows and grows
and....

Also, I should have mentioned that I am manipulating the database via
Java and the jdbc driver. I suppose I could close the database and do a
system call to launch a VB application that does the compression and
then reopen the database after it is finished.

I was hoping for something like the 'vacuum' command in Postgress.

Any other ideas?

thanks.
lynn

David W. Fenton wrote:
"Chuck Grimsby" <c.*******@worldnet.att.net> wrote in
news:11**********************@g47g2000cwa.googlegr oups.com:

Of course, the rest of that answer is that if you're just going to
expand the database by adding new records to it, what's the point
of the compact? Access _will_ reuse unused space.

Well, it doesn't use it efficiently. After a certain amount of time,
fragmentation of index pages can lead to significant performance
degradation.

Indeed, other than just plain old preventative maintenance, I think
that re-writing index pages for the performance gain is probably the
most important thing that regular compacting accomplishes for you.


Nov 13 '05 #7

P: n/a
Tom van Stiphout wrote:
On Wed, 01 Jun 2005 19:02:19 -0600, Lynn <Fo************@comcast.net>
wrote:

The system call would be:
<path_to>msaccess.exe <path_to>my.mdb /repair /compact
You can run this command using the Windows scheduler, at a time where
nobody is in the db.
Lynn wrote: Is there a way to compress this mdb file without using the Access GUI?
Is there an SQL command that will do it? I don't want to pay the price
of msaccess.exe just to perform this maintenance task.


If you have DAO then use the CompactDatabase method.

I have a program that'll do this either as a GUI or a command line
interface, if you already have the DAO bits on your PC then the
executable is 36KB otherwise it's around 3.8MB for the install package.

If you're interested I'll make it available for download somewhere.

--
[OO=00=OO]
Nov 13 '05 #8

P: n/a
On Thu, 02 Jun 2005 08:31:17 +0100, Trevor Best <no****@besty.org.uk>
wrote:
Tom van Stiphout wrote:
On Wed, 01 Jun 2005 19:02:19 -0600, Lynn <Fo************@comcast.net>
wrote:

The system call would be:
<path_to>msaccess.exe <path_to>my.mdb /repair /compact
You can run this command using the Windows scheduler, at a time where
nobody is in the db.


Lynn wrote:
Is there a way to compress this mdb file without using the Access GUI?
Is there an SQL command that will do it? I don't want to pay the price
of msaccess.exe just to perform this maintenance task.


If you have DAO then use the CompactDatabase method.

I have a program that'll do this either as a GUI or a command line
interface, if you already have the DAO bits on your PC then the
executable is 36KB otherwise it's around 3.8MB for the install package.

If you're interested I'll make it available for download somewhere.

--
[OO=00=OO]


Hi
You can also do it from vbscript, see for example
http://www.motobit.com/tips/detpg_CompactMDB/
(3.5 for 97)
David
Nov 13 '05 #9

P: n/a
Lynn <Fo************@comcast.net> wrote in
news:d7**********@iruka.swcp.com:
I am doing a DELETE several times a day of all records older than
a specific date. I am never using this extra space. It grows and
grows and....


Why are you doing that?

I have never run across an application where there was a valid
reason to delete records like. The only scenario I can imagine would
be if you're dealing with millions of records, and deleting a day's
worth of records clears out 100s of thousands of records.

If you aren't adding more than 10s of thousands a day, then you
really aren't gaining anything at all by deleting the records,
instead of filtering them out for your application's needs.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.