Command Button to Compact Database

Brott

Board Regular
Joined
Dec 12, 2002
Messages
110
After messing with this I am just going to ask.
What script would I use if I wanted the user to be able to compact a database.
I did have it set to compact upon closing, I am going to change this to have it executed when clicking a button.

Any help would be appreciated.

Because multiple user may be in the database I believe it would not be able to compact so any errors with compacting should just be ignored and allow nothing to happen. Just return the user to the screen.

Thanks in advance everyone!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
You can provide your users with a shortcut that compacts the database thru the command line. Create a shortcut and in the target field enter the following:

"PathToAccess" "PathToDatabase" /compact

so for me pathToAccess would be "C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE"

and PathToDatabase "C:\Documents and Settings\Owner\My Documents\Giacomo's Documents\MyBigFatDatabase.mdb"

check the file size before and after to confirm that it was compacted.

hth,
Giacomo
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi Brott

You won't be able to compact the database using a button on a form within the database itself. You can however do this where you have split the database and the 'compact' button for the back end is located on a form in the front end.

Where there aren't too many users I often use the 'Compact on Close' method like you suggested. Another option is to use the script outlined by giacomo and use scheduler to run this script overnight at a time when the users will be out of the database (eg 2am) - the PC will need to be running for the scheduled event to run.

HTH, Andrew
 

Brott

Board Regular
Joined
Dec 12, 2002
Messages
110
Both of these suggestions are great, however only one sounds possible for my situation. (the first one)
Is there anyway you can elaborate just a tad more on how to accomplish this methods?
Don't be afraid to make it at a 3rd grade level. This way I don't have to ask to explain something else further.
Every Friday at 5pm there script that is ran and pulls data from two separate systems (non MS) and puts all the data into text files. In my Database an operator selects one of the options on a form which will delete records then populate the multiple table with the data from the text files. (This is where the file grows to 700+ megs).
It is after this event that takes place is where I would like to have the compacting be executed by the operator. (The database is set up so that the operator has no access to another options then what buttons are on the form. Idiot proofed for lack of better words.) From here this file is then pushed out through the network to 50 plus tough books and onto another server for everyone else to have access to. Splitting the database it not really an option. For all the people with the file on their tough books when they close the database it would not be a problem for me to set up compacting upon closing the file.
My issue is with the copy residing on the network where many people have access to it. As you already know when two people have the file open upon one closing it the file will not compact. Here stands my problem with having the file compact upon closing.
I know this was a long read and I do appreciate your guidance in handling an issue like this. It is not detrimental for me to do this but I would like to reduce the unnecessary use of our resources.
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
Brott,
giacomo spelled it out completely. If you don't know how to create a shortcut on your desktop, then do a search on Google.
I'm assuming you just did not recognize that he gave you the whole solution. It is: "PathToAccess" "PathToDatabase" /compact
Then he spelled out what the PathToAccess is on his machine. You need to use whatever the PathToAccess is on your machine. Same thing for the PathToDatabase. The "/compact" is the command to Access to compact the database. Sorry, but it does not get much easier or better than that.
 

Brott

Board Regular
Joined
Dec 12, 2002
Messages
110
Thanks for that insight full help.
And you were correct I did not know what it meant.
To some this may have been common knowledge, I just have never done that before (included some type of executable at the end of a shortcut).

Thanks yet again.
 

Forum statistics

Threads
1,136,354
Messages
5,675,291
Members
419,559
Latest member
BraytonM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top