VBA to Compact or Backup an Access database?

C

Charlie Brown

I noticed the "acCmdBackup" and "acCmdCompactDatabase" listed in the
RunCommand reference. When I tried to use them I received an error that you
can not Compact an open database from VB Code. The acCmdBackup simply stated
it was not available.

Can VBA be used to run utilities like Backup and Compact from a click event?
Anyone have some code suggestions to help me save some time? Thanks!!
 
6

'69 Camaro

Hi, Charlie Brown.
Can VBA be used to run utilities like Backup and Compact from a click event?
Anyone have some code suggestions to help me save some time?

If you're using Access 2000 or later, then yes, VBA code can be used to
compact the current database. Please see the following Web page's VBA
section for a link to the tip, "How to compact the current database
automatically when it reaches a certain size" for instructions and the code:

http://www.Access.QBuilt.com/html/how-to_tips.html

If you need to compact another database from the current one, then please
see the same Web page in the Fix-its section for a link to the tip, "How to
compact a database using the command-line switch," which can be used in a VBA
procedure using the Shell( ) method.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
See http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
C

Conrad

I have always compacted from another Access instance. For example.

remotedbs.CompactRepair "C:\MyDB.mdb", "C:\MyDB_Compacted.mdb"

Unfortunately you then have to delete the old uncompacted database and
rename the new compacted database.

If anyone has a better way of doing this please let me know. This is how a
maintain any of our databases.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top