By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,308 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

how to compact and repair access 2010 database from form on button click

P: 62
I am hiding title bar and file button on it,because of which user can not compact and repair database.so want to create button on form using which user can compact and repair.
I tried code.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunCommand acCmdCompact
  2.  
But it gives error that instead of code/macro do it from file menu
so what code i should write and is it possible
Thanks in advance
Nov 22 '13 #1
Share this Question
Share on Google+
4 Replies


zmbd
Expert Mod 5K+
P: 5,332
If you are trying to do this on the open database this will error because the database has to be closed. When you do this from the command/ribbon, the Program closes the file and does its magic. When you try to do this from VBA, the file closes, and then the Program forgets what it was doing...

The only ways I can see that you could potentially do this is to start another instance of the Access application that had code to check that the calling application has closed, then you could potentially call the C&R or maybe shell out run a script.

You really should not need to C&R a live database if it is properly designed. If you are importing and deleting a lot of data, then I would suggest that you split the database out. You can then then do some magic with that backend database file.

(you'll find one discussion about this here: http://bytes.com/topic/access/answer...base-using-vba as you can see it's not something that is easily done)
Nov 25 '13 #2

zmbd
Expert Mod 5K+
P: 5,332
as this thread popped up again due to spamming:

Please do not post commercial solutions/programs in answer to this thread - such posts will be deleted and the poster's account may be banned (either short term or permanently) for violating the terms of use for this site.
Mar 29 '14 #3

P: 62
thanks z
i simply checked the box compact on close in
File>>Options>>Current databse>>Application options

it works well for me i also split the database from your suggestion
regards
Aug 28 '14 #4

zmbd
Expert Mod 5K+
P: 5,332
rahul2310

Auto Compact and Repair has had issues and caused corruption in databases both the FE and the BE. If you are having alot of database bloat then there is something wrong with your application and Auto Compact and Repair may just make it worse. Instead, you need to find the source and restructure the code and tablses so that you do not get that kind of bloat. You can start a new thread and tell us about your application and we may be able to help you avoid the bloat.
Aug 28 '14 #5

Post your reply

Sign in to post your reply or Sign up for a free account.