[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

What causes bloating of .mdb files?

Posted on 2009-04-22
3
Medium Priority
?
244 Views
Last Modified: 2013-11-29
I have an Access2003 database with about a dozen tables, at least one of them some 250,000 rows of fields and several other tables with 3,000 rows.  Each time I use the database I notice that the size seems to gradually increase.  When I run "compact and repair" the difference in size is dramatic (from 300,000KB to 70,000).  I'm certain that it runs faster at the smaller size.

What is likely causing this bloat?  Do you recommend that I encode "compact and repair" into the code for my "Exit Application" button?  I need some experienced advice.

Thanks!
0
Comment
Question by:Greg-C
  • 2
3 Comments
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24206329
Every time a record is deleted, it leaves space in the database. You have three choices on this issue: set your DB to compact on close, regularly (daily, weekly, etc.) run a compact and repair during the hours no one is using the DB, or schedule a compact and repair during the off hours.
0
 
LVL 16

Accepted Solution

by:
Chuck Wood earned 2000 total points
ID: 24206417
Here is an example of a line of a .CMD file that I schedule to run at 1:00 AM every day to compact the database.

"C:\Program Files\Microsoft Office 2000\Office\jetcomp.exe" -src:"PathToSourceDb" -dest:"PathToDestDb"

Open in new window

0
 

Author Closing Comment

by:Greg-C
ID: 31573350
I appreciate your insight.  I'll set up some sort of schedule and have the system remind the client when a compact is due (perhaps "time since last".  Looks good.  Thanks!
GregC
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question