Add code to that file so we can compact & repair our database without having it open.
Programmatically create a VBScript file.
As I just said, the main problem is that the current database has to be closed. Well, some things are, but this isn't one of them, if you're willing to do some weird trickery. If you run compact & repair, it moves the data to a database called Database.accdb in your current folder (name might vary based on existing names/database type), then deletes your current database, and then renames the new one. You might also notice the Access compact and repair button doing something similar. Run the first part of your batch, close the file, compact and repair, reopen, run second partĭBEngine.CompactDatabase fileLocation, fileLocation & "_1" My advice: either run the process from an external database, a VBScript file or PowerShell. You might notice the Compact and repair button on the ribbon requires an exclusive lock, closes the database, then compacts and repairs, and then reopens it. As such, you can't compact and repair a database between steps in a sub or procedure, since the database is open when running the procedure. Compacting and repairing a database requires the database to be closed. _Ĭontrols("Compact and repair database.").accDoDefaultActionĪpplication.SetOption "Auto compact", True Set control = CommandBars.FindControl(Id:=2071)ĬommandBars("Menu Bar").Controls("Tools").Controls("Database utilities"). The 'auto compact' causes the database to compact on closing, it does NOT allow the compaction of the database to be added between steps. I've found several threads on this subject, but they're all three of four years old (or more) and the methods they describe don't seem to work anymore.Īre they're any solutions that work with Office 365 (version 1720)? If I use my automated VBA script (without compacting) then it crashes halfway through when the database busts the 2Gbyte limit. If I do the process manually (including compacting) then everything works fine and I end up with a 800MByte database. The problem is that a couple of the steps (the UPDATEs) temporarily increase the size of the database which can cause problems with subsequent imports. The process isn't rocket science, but there are several steps so it really does needs to be automated. I have a batch process that I run occasionally, it drops a few old tables, re-imports them from other databases, renames a couple of fields, does a few updates and makes a few other minor changes. I want to be able to run the 'compact and repair' process from within a VBA module in the database.