I was inserting data into a MS Access database using JDBC-ODBC driver. The blank mdb file was 2KB. After populating this database, the size grew to 155MB. Then I was deleting the data. But I found the size of mdb remains the same as 155MB. I don't get any errors. But is it normal this way? I would expect the file size reduces. If it is designed in this way, what is the idea behind it? Thanks
views:
3509answers:
5
+13
A:
MS Access doesn't free up space used by records even after they are deleted. You can free the space manually when you need to or automatically each time you close the application.
To do it manually, use the Compact and Repair utility:
Backup your database, as there is a bug in Access 2007 that may delete your database during the compacting procedure.
If you are compacting a multiuser (shared) database that is located on a server or shared folder, make sure that no one else has it open.
On the Tools menu, point to Database Utilities, and then click Compact and Repair Database.
To do it automatically when you close the application:
Open the database that you want MS Access to compact automatically.
On the Tools menu, click Options, and then choose the General tab.
Select the Compact On Close check box.
After deleting the data and compacting the database don't be surprised if is still larger than 100 KB. There is a certain amount of overhead that cannot be removed after you add data the first time.
Also, beware that AutoNumber field values behave differently than advertised after the compacting procedure: According to the MS Access 2000 documentation, if you delete records from the end of a table that has an AutoNumber field, compacting the database resets the AutoNumber value. So the AutoNumber value of the next record you add will be one greater than the AutoNumber value of the last undeleted record in the table.
I have not found this to be the case: If you have 100 Autonumbered records and delete the last 50, the next AutoNumber record (according to the documentation) should be numbered "51". But in my experience it is numbered "101", instead.
flamingLogos
2008-10-01 05:30:42
One should back up any MDB before compacting, not just in Access 2007, because some rare forms of corruption can cause data that is still accessible in the pre-compacted db to be lost in the compacted data file. This is on reason why the Access COMPACT ON CLOSE "feature" should always be turned OFF.
David-W-Fenton
2009-01-02 03:06:30
Not only does compacting 'prevent the bad stuff from happening' it also promotes the good (assuming you've chosen your PKs wisely!): "If a primary key exists in the table, compacting restores table records into their primary key order. This provides the equivalent of Non-maintained Clustered Indexes, and makes the read-ahead capabilities of the Microsoft Jet database engine much more efficient. Compacting also updates the table statistics within the database that are used as Jet optimizes queries." (http://support.microsoft.com/kb/288631)
onedaywhen
2009-05-18 09:04:49
+20
A:
MS Access doesn't reclaim the space for records until you have compacted the database.
This is something you should do to an access database as part of your regularly maintenance otherwise you will end up with some pretty painful problems.
You can compact a database either through the MS Access UI (Tools -> Database Utilities ->
Compact and Repair Database) of you can use the command prompt using:
msaccess.exe /compact target database
Jon Cahill
2008-10-01 05:32:10
A:
I agree with flamingLogos, you can also compact the db from the calling app, if you have access to the code.
Saif Khan
2008-10-01 06:51:11
A:
The first stop, as mentioned should be attempting to compact/repair the database. However you can also get some size saving past that by creating a new database and importing all of the objects from the old. Past that, converting it to an MDE should get you a hair more. As always, don't play around with your production copy. Also if you go with an MDE, make sure you have properly split the database first. (And of course keep a copy of the source MDB should you need to make modifications in the future.)
Oorang
2009-05-17 09:47:26
If you are only using an MDB to store data then making it an MDE won't make a difference as there is no human readable VBA code to remove. That said in BE MDBs I included a single form on startup that tells the users to not make any changes and includes the database version number. Which is a value I keep in a table for use when programmatically update the tables, fields, indexes and relationships when the user gets a new FE.
Tony Toews
2009-10-26 21:31:53
A:
You can compact the database from code using JRO. See: http://support.microsoft.com/kb/230501
Andreas
2010-02-17 10:32:52
You don't need JRO to compact a database -- DAO works just fine and in a normal Access app won't require adding a reference to JRO (or coding with late binding). JRO may be useful if you're working with Jet/ACE from outside Access and are already using ADO, but if you're within Access, DAO is preferred.
David-W-Fenton
2010-02-18 05:23:05