Search code examples
vbadatabasems-access

MS Access: how to compact current database in VBA

Pretty simple question, I know.

Solution

  • If you want to compact/repair an external mdb file (not the one you are working in just now):

    Application.compactRepair sourecFile, destinationFile

    If you want to compact the database you are working with:

    Application.SetOption "Auto compact", True

    In this last case, your app will be compacted when closing the file.

    My opinion: writting a few lines of code in an extra MDB "compacter" file that you can call when you want to compact/repair an mdb file is very usefull: in most situations the file that needs to be compacted cannot be opened normally anymore, so you need to call the method from outside the file.

    Otherwise, the autocompact shall by default be set to true in each main module of an Access app.

    In case of a disaster, create a new mdb file and import all objects from the buggy file. You will usually find a faulty object (form, module, etc) that you will not be able to import.