File size does not shrink after DELETE, TRUNCATE, or DROP operations

[Jefferson Poe]

Hello,

I'm using UCanAccess 4.0.2, and I noticed that after DELETE, TRUNCATE, or DROP operations, the file size of the target .mdb file does not shrink?

Moreover, if I ran a DELETE operation on a table that does not have a primary table, subsequent DELETE operations will take longer.

These problems go away after opening the .mdb in MS Access and performing a "Compact" operation.
Are there ways to shrink the file size / reset DELETE operations via UCanAccess?

Thanks!

[Gord Thompson]

I noticed that after DELETE, TRUNCATE, or DROP operations, the file size of the target .mdb file does not shrink

 
That is true, not only for UCanAccess but also for Microsoft ACE/Jet and many other database engines. The space within the database file is freed for later re-use, but the file itself does not shrink. Shrinking the database file is a separate operation, e.g., "Compact and Repair Database" for Access, DBCC SHRINKDATABASE for SQL Server, etc..

Are there ways to shrink the file size / reset DELETE operations via UCanAccess?

 
No. This has been discussed before with respect to both UCanAccess and Jackcess, but it is unlikely to be implemented any time soon.

However, you can have your Java application create a process that calls the Windows CSCRIPT.EXE utility to run a VBScript along the lines of

Set dbe = CreateObject("DAO.DBEngine.120")
dbe.CompactDatabase "C:\path\to\original.mdb", "C:\path\to\compacted.mdb"

to compact the database for you.

[Jefferson Poe]

Ok, then. Thanks for the reply, Gord!