• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 933
  • Last Modified:

Is there VBA code equivalent of the Compact and Repair Database action using Access 2003?

I am developing an application in Access 2003. I realize that I can go to the menu item
TOOLS -> Database Utilities -> Compact and Repair Database to compress the size of my application. Is there a way I can accomplish this task by setting up a command button to click. And as a result of the click event, write VBA code that would accomplish the compression.

In other words, is there VBA code equivalent of the Compact and Repair Database action?
Or do you have to manually perform this action?
0
zimmer9
Asked:
zimmer9
1 Solution
 
zimmer9Author Commented:
Okay, I see the following code.
Is there a way to compress to the same filename?
---------------------------------------------------------------

'Try this code. It will compress a .mdb

Function CompactMyDB()

'File name
Const ConFileName = "Cashbook Tables.mdb"
'Path name to database folder
Const ConFileFolder = "C:\Program Files\Database\"
'Path name to backup folder
Const ConBackUp = "A:\"

'Compress to a A Drive
DBEngine.CompactDatabase ConFileFolder & ConFileName, ConBackUp & ConFileName
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You can't really compact the open db you are in:

CompactDatabase
"Copies and compacts a closed database,"
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
And there is Michael Kaplan's utility:

TSI SOON (Shut One, Open New) database add-in
http://www.trigeminal.com/lang/1033/utility.asp?ItemID=8#8
0
 
hnasrCommented:
I am using Access 2010, it gave me this message when issuing
 
  RunCommand acCmdCompactDatabase

Open in new window

"You cannot compact the open database by running a macro or Visual Basic code.
Instead of using a macro or code, dick the File tab and then dick Compact and Repair Database."

But there should be a way, issuing commands to Ribbon. Didn't practice with that yet.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now