Bookmark page
Printable version
Course updatesForum home » Delegate support and help forum » Microsoft Access VBA Training and help » Compact and Repair
|
|
|
Bookmark this page|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Compact and Repair
Resolved · Low Priority · Version 2003
Reply
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
For upcoming training course dates see: Pricing & availability
RE: Compact and Repair
Hi AshleyThanks 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 SubRegards
Stephen
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
RE: Compact and Repair
Hi AshleyThanks 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
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
|
 |
|
» Forum post: Charts using 3 sets of data |
Hi, please could you let me know how to create a bar chart using 3 sets of data. e.g. the bottom axis would have the categories O&G, M&M, Chemicals, Utilities, up the side would be the numbers, but i want to split the bars up into different colou..
