CODEWITHSUNDEEP
ms-accessms-access-2007vba
arcotenterprises
arcotenterprises

Reputation: 131

How to wait for Compact Repair - Access VBA

Have a VBA Module in Access 2007 which performs similar actions in a For loop. At the end of every loop I wish to compact and repair the current database before proceeding to the next iteration.

Reason : In every iteration I create , use for calculation and delete a table. The size should remain under control.

Using SendKeys , I cannot compact and repair - if calling the module from a Form Button. Any trick to ensure that the SendKey works fine and the Module continues onto the next iteration flawlessly. Some idea to control Form loading / Module execution / Checking Status etc. End Purpose being to wait just long enough till the Compacting is done and then move on. Also how to safely ensure that SendKeys / (suggest alternate) for compacting works fine.

Thanks

Upvotes: 2

Views: 4126

Answers (4)

Dan Metheus
Dan Metheus

Reputation: 1438

You cannot compact and repair the current database in a loop of any kind. You could compact and repair an external database in a loop, or you could compact the current database on close.

Upvotes: 2

HansUp
HansUp

Reputation: 97131

"... in a For loop. At the end of every loop I wish to compact and repair the current database before proceeding to the next iteration."

Consider what actually happens when you compact the current database. Access first creates a compacted version of the current database as a new db file. Then it deletes the old db file, renames the new file to the old name, and finally opens the new db file.

So if your code attempted compact each cycle through a For loop ... when Access then opens the compacted db file ... how would it know you wanted it to continue in that For loop?

If you truly want to do something like that, you will have to store a value to record which was the last iteration of the For loop. Then create an autoexec macro to retrieve that value at database open, and enter the For loop at the appropriate cycle. And then decide whether you really want that all to happen every time the database is opened.

However that seems like too much effort to me. It should be simpler to use another db file to hold your volatile data. Then from code in the current database you can use DBEngine.CompactDatabase to compact the external db file.

Upvotes: 7

Renaud Bompuis
Renaud Bompuis

Reputation: 16806

I've already answered your other question (which is quite similar), but for the sake of helping others I have published a simple utility function that helps you restart and compact the current database:

To ensure that you continue the proper iteration after you restart:

  • save the state of your operation (for instance the loop count) in a LocalSettings table (for instance) in your database.
  • Restart.
  • Use an autoexec macro to launch the function that does your work: it should load the operation/loop count from the LocalSettings table and carry-on.

Another solution is to modify the restart function to pass a custom command line argument to the Access application that you can check when the app restarts.
You can easily check for command line arguments, see the VBA Command function.

Upvotes: 0

Tom Collins
Tom Collins

Reputation: 4069

If you're doing that much compact & repair, then I would split the DB into a front-end and a back-end. All your tables will be on the back-end. Splitting the DB is pretty standard for any regular Access programmer anyway.

Then when you want, use the Shell command to open the back-end DB with the /compact switch. This will open it, do a compact & repair, and then close it.

So, your code will be something like this:

Ret = Shell("C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE /compact c:\MyFolder\MyDB.accdb")

If you need to wait for it to finish, then use the WScript.Shell command instead.

Good Luck

Upvotes: 2

Related Questions