Compact and Repair

Resolved · Low Priority · Version 2003

Fri 25 Sep 2009, 15:19 Delegate Ashley said...

Ashley has attended:
Access VBA course

Compact and Repair

I am often automating a large number of queries and table buildings in Access VBA and setting these to run in code. After building each main table I need to compact and repair before automatically running the next set, although the warnings within access can be turned off (query x will update y number of rows etc) there is a message during the compact and repair saying something like 'database cannot save whilst vba open' which i cant seem to turn off and means i cant leave my processing to run and go get a coffee as i would like!

Many Thanks

Wed 30 Sep 2009, 14:38 Edited on Wed 30 Sep 2009, 14:39 Trainer Stephen said...

RE: Compact and Repair

Hi Ashley

Thanks for your question

After some research I have found the following code which compacts and repairs the data base with no warning or error messages.

I suggest running it first on a test database, and then backing up the main database before you try using it. If this doesn't work, please feel free to get back to me

Public Sub Compacter()



   CommandBars("Menu Bar").Controls("Tools").Controls("Database utilities").Controls("Compact and repair database").accDoDefaultAction



End Sub

Regards

Stephen

Thu 1 Oct 2009, 10:00 Delegate Ashley said...

RE: Compact and Repair

I tried implementing your code however despite it being different to what i had written out the same problem occurs. The Error message I recieve is the following:

'You Can't Compact the running database whilst running a macro or visual basic code'

I understand that the code you provided seeks to simulate the rest of the error message by getting the VBA to think it is clicking on the button within Access however it still see's it as VBA being run.

I have tried running the Processing module both directly from the code and by closing the VBA window and running the processing directly from a macro in Access.

Its not the end of the world if it cant be done automatically, its just a nice to have as would keep the file sizes down and speed the processing up a little.

Many Thanks

Sun 22 Nov 2009, 11:36 Trainer Stephen said...

RE: Compact and Repair

Hi Ashley

Thanks for the followup

First I apologise for the delay in responding to you.

After reviewing the problem, I can find no easy method to do this using code

However, it is of course possible to set the database so that it automatically compacts on closing.
Simply go to Tools-Options-General)to automatically compact upon closing.

Would this meet your needs?

Regards

Stephen

Mon 23 Nov 2009, 08:08 Delegate Ashley said...

RE: Compact and Repair

Stephen,

Thanks for the response, I have been running the Compact and Repair from VBA at the end of my processing for a while and its not been too much of a problem, just a shame it cant c&r automatically part way through some things as i can see the process bar getting slower and slower over time.

Many thanks for looking into the problem for me

Ashley

Training courses