Why do mdb files get so big as they're in use?

  • Thread starter Thread starter John S. Ford, MD
  • Start date Start date
J

John S. Ford, MD

I have MS Access database (Ac2000) and it gets remarkably big as it's in
use: 2MG -> 7MG even though not that much data is being added.

Why does this happen and is there a way to automate the "compact" process?

John
 
I have MS Access database (Ac2000) and it gets remarkably big as it's in
use: 2MG -> 7MG even though not that much data is being added.
Click to expand...

In use meaning when you're doing database development? Or in use by a
non-developer user? Both?
Why does this happen and is there a way to automate the "compact" process?
Click to expand...

In another post you mentioned creating/deleting a temporary table. When
you do that, the MDB file size expands to hold the temporary table data.
But deleting the table (or just data from the table) doesn't reclaim
the vacated space until you compact.

If you're serious about automating compaction, search previous posts to
see what the MVPs have to say about that subject. Be cautious. You
should probably not set the database option "compact on close".

Hans
 
because Jet is a piece of shit. it bloats.

if you want to deal with something more practical-- then move to SQL
Server
 
Acccess uses temporary space for many actions. The temporary space is
usually marked as trash, but since the primary concern for a db application
is performance, Access does not clean up the trash until the user
specifically tells it to- by compacting the db. This is no different than
most other databases, except Access combines the data storage, application
code, forms, reports, etc. all into one file so any update of any of those
elements, or temp space needed while running a query, cause more trash to be
created.

Since most of the trash is created in the application portion, not the data
storage portion, one way to both improve performance and reduce bloating is
to separate the front end (forms, reports, code, queries) from the back end
(tables only). The front end uses linked tables to connect to the back end.
This is the only recommended scenario for multi-user Access databases since
it improves performance and reliability. Now most of the bloat is in the
front end, which can simply be replaced as desired. I setup clients to run
an Access application by executing a simple command file which copies the
current front end mdb from the server and then executes that local copy. So
every time they start the app it starts with an unbloated front end.
Application updates are automatic- copy the updated application mdb to the
server and each user gets the update the next time they start the
application. Splitting the db lets the developer update the application
without having to worry about the data, which lives in the separate backend
db.
 
On Tue, 24 Mar 2009 22:02:01 -0700, "John S. Ford, MD"

7 MB is not remarkable. It is very small. Your harddrive is probably
at least 1,000 times that size.
You can automate a compact by scheduling a task, and running
msaccess.exe with the /compact command line switch. The help file has
more info on command line switches.

-Tom.
Microsoft Access MVP
 
I have MS Access database (Ac2000) and it gets remarkably big as it's in
use: 2MG -> 7MG even though not that much data is being added.

Why does this happen and is there a way to automate the "compact" process?

John
Click to expand...

As Tom says, 7MByte is tiny. You're limited to 2048 MByte so you have some
room.

However... the bloating is ongoing. Every time you create and delete a
temporary table, the space it occupied is waste and will not be reused. I'd
question the need to create temp tables AT ALL - it is actually rather rarely
necessary! For *most* (not all, obviously) applications you can use a Select
query as the source for a report, or a form, or an export; there's no need to
create a separate table for any of those purposes.

If you have a *DEMONSTRATED* requirement to do so, consider creating a new
"scratchpad" database on the fly just to hold the temp tables. This .mdb file
can be created, used for a linked temp table, and then deleted in the course
of a session. See http://www.granite.ab.ca/access/temptables.htm or
http://www.tek-tips.com/faqs.cfm?fid=5980 for detailed instructions.

As noted elsethread, you should *certainly* be using a split database here.
 
John W. Vinson said:
As Tom says, 7MByte is tiny. You're limited to 2048 MByte so you have some
room.

However... the bloating is ongoing. Every time you create and delete a
temporary table, the space it occupied is waste and will not be reused. I'd
question the need to create temp tables AT ALL - it is actually rather rarely
necessary! For *most* (not all, obviously) applications you can use a Select
query as the source for a report, or a form, or an export; there's no need to
create a separate table for any of those purposes.

If you have a *DEMONSTRATED* requirement to do so, consider creating a new
"scratchpad" database on the fly just to hold the temp tables. This .mdb file
can be created, used for a linked temp table, and then deleted in the course
of a session. See http://www.granite.ab.ca/access/temptables.htm or
http://www.tek-tips.com/faqs.cfm?fid=5980 for detailed instructions.

As noted elsethread, you should *certainly* be using a split database here.
Click to expand...
 
John W. Vinson said:
As Tom says, 7MByte is tiny. You're limited to 2048 MByte so you have some
room.

However... the bloating is ongoing. Every time you create and delete a
temporary table, the space it occupied is waste and will not be reused. I'd
question the need to create temp tables AT ALL - it is actually rather rarely
necessary! For *most* (not all, obviously) applications you can use a Select
query as the source for a report, or a form, or an export; there's no need to
create a separate table for any of those purposes.

If you have a *DEMONSTRATED* requirement to do so, consider creating a new
"scratchpad" database on the fly just to hold the temp tables. This .mdb file
can be created, used for a linked temp table, and then deleted in the course
of a session. See http://www.granite.ab.ca/access/temptables.htm or
http://www.tek-tips.com/faqs.cfm?fid=5980 for detailed instructions.

As noted elsethread, you should *certainly* be using a split database here.
Click to expand...
 
John,

I'm certain that I do NOT have a "demonstrated" need for using a make-table
query. Unfortunately I've been singularly unable to figure out a way to
change this. I've posted about my problem and application in the
access.public.queries under the title of "Converting a query to a WHERE
condition".

As I mentioned there, I'm sure I could do this with subqueries...IF I
understood them!

John
 
Back
Top