Wouldn't MS Access(.mdb) file size reduce after deleting the content of database?

Question

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

Answer 1

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 "target database.accdb" /compact  

N.B. the /Compact switch must be after the target database

Answer 2

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:

1. Backup your database, as there is a bug in Access 2007 that may delete your database during the compacting procedure.

2. 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.

3. On the Tools menu, point to Database Utilities, and then click Compact and Repair Database.

To do it automatically when you close the application:

1. Open the database that you want MS Access to compact automatically.

2. On the Tools menu, click Options, and then choose the General tab.

3. 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.