472,371 Members | 1,495 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,371 software developers and data experts.

Compact and Repair


The more I use an application ( an mdb) created using MSAccess I notice
that the Byte size of the application keeps on increasing. Once in a while I
do a Compact and Repair Database (under Tools in MSAccess) on the
application and the size then returns to normal.

What is causing the mdb to keep on increasing in size?

Is it a good idea to do a Compact and Repair Database every time the
application is closed so to keep the size of the mdb to a minimum?

If Yes what is the best way to do a Compact and Repair Database using code?


G Gerard
Oct 27 '06 #1
3 2079
Sounds like you're creating temporary objects in your database.
That'll make it grow. You can set it to Compact and Repair on close,
which should keep it small. If you're creating temporary objects , you
might want to look at Tony Toews' site


Oct 27 '06 #2

"G Gerard" wrote
What is causing the mdb to keep on increasing in size?
Access does not return to the system the disk storage of objects that have
been deleted or replaced until you do Compact and Repair.
Is it a good idea to do a Compact and Repair Database
every time the application is closed so to keep the size
of the mdb to a minimum?

It's not necessary, generally, to compact every time you open your DB. It is
a good idea to split the DB into application (front-end: queries, forms,
reports, macros, and modules) and data (back-end: tables, relationships, and

When you do, and can tell us which is growing, then someone may be able to
suggest ways in which you might be able to determine the cause and possibly
avoid some of the bloating. And, which you will need to Compact and Repair
most frequently.

Larry Linson
Microsoft Access MVP
Oct 30 '06 #3
"G Gerard" <gg*****@nbnet.nb.cawrote in
Is it a good idea to do a Compact and Repair Database every time
the application is closed so to keep the size of the mdb to a
Access 2000 and later provides the "Compact on close" option.

I would recommend that it NEVER BE USED.

First off, if your app is properly structured, you're opening only
the front end. Front ends may bloat a bit, but since they have no
data in them, the bloat is finite, and so there's no compelling
reason to ever compact a front end (except to update the table stats
that are cached in the table links; that is, if you add 100K records
to a 10-record table in the back end, it would be a good idea to
compact the front end to update its metadata about that back-end
table; this metadata is used in optimizing queries and for other
purposes, such as helping the Rushmore data retrieval technology
decide how to retrieve data).

And COMPACT ON CLOSE is downright *dangerous* when opening a back
end because sometimes a compact to a damaged MDB will lose data that
is still accessible before the compact.

So, I would say:

Always keep COMPACT ON CLOSE turned off.

David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Oct 30 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: KEVIN97810 | last post by:
Hello to all, Assume my mdb name is Cust.MDB. I want to compact & repair the same Cust.MDB when the user exit the program. Is there a way you can do it in code behind the EXIT button. I am...
by: Paolo | last post by:
Hi, I am trying to compact and repair my database, however every time I try it comes up a message saying: Table: "TempMSysAccessObject already exists", whenever I try to look for this table I...
by: Ken Winters | last post by:
Whenever I run the Repair/Compact on my database, the security settings on the file change. A 3rd party application can only access the database over the network when the username "Everyone" is...
by: Wayne | last post by:
Does "Compact On Close" do a "Compact and Repair" or just a compact. Is a compact necessary (or at least a good idea) on a regular basis, say weekly, for a database that has several hundred records...
by: GaryDave | last post by:
My school registration database has not been quite right after a recent compact and repair (done while I was away). Though most of the many forms and subforms are working normally, one form in...
by: ApexData | last post by:
Hello I have finally completed an application and am preparing to install it on a network of 10 users. The application will be split FE/BE. The application has its own login, which controls...
by: Ron | last post by:
Hi All, Using WinXP pro/Access 2000. I have a database that's been used for about 5 months. Transferred lots of data from a dos based program, then the users have been using it for that 5...
by: Ron | last post by:
New discovery. If I take a perfectly good database, and "compact/repair" on it with Access 2000 (seems to be at multiple sites--I've tried it with my system here, at another office on an...
by: Bizmark | last post by:
Access2003, Windows XP Pro OEM user OS, Windows Sever 2000 server OS. Not-so-recently I inherited 3 Access DB's from another company merging with our own. Our own system works off of SQL...
by: zufie | last post by:
When I Repair/Compact my Access databse. I get icons/copies of a database named db1.mdb, db2.mdb, db3.mdb, etc... How else may I repair this database? Thanks!, Zuf
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and credentials and received a successful connection...
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...
by: jack2019x | last post by:
hello, Is there code or static lib for hook swapchain present? I wanna hook dxgi swapchain present for dx11 and dx9.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.