Compacting Database 1

[fredk]

I am just looking for some more information/insight recarding the compacting of a database. I have always compacted the old fashion way and was looking to automate the process a little. I have several front end databases and I am looking to have each persons database compact when the close the database.

I have searched the faq and other posts regarding this topic and found some interesting information - I found the following code in a previoius post:

Public Function MyCompDb()
On Error GoTo MyCompDb_Err
SendKeys "%(tdc)", False
MyCompDb_Exit:
Exit Function
MyCompDb_Err:
MsgBox Err.Number & " : " & Err.Description, vbCritical, "Unexpected Error"
Resume MyCompDb_Exit
End Function

I placed this code in a module and tried calling it in the onclose event of the main form that is used.

I was looking for any input on a better place to house this code? Also, when a database is compacted, I understand Access copies then deletes the database but renames the new database the same as the old name (seemless) But I saw some information that indicated that you could not compact a database while it is open. I don't understand that as you are in the database even when you chose to manually compact via Tools/Database Utilities.

Any feedback/information would be greatly appreciated!

Thanks!

Fred

 

[mikey69]

If you have Access 2000 or later, there is an option to have the program compact on closing. But this will only deal with the front end. You'd have to open the back en seaprately and close it. And that is where compacting will be most often needed. It is possible to compact programmatically, but I've never found it worth the effort of grappling with the code.

If you are really stuck, post again and i'll try to give a bit more info.

 

[oharab]

Have a look at my FAQ: faq705-1955

It's how I do it with A97. There's no reason why it couldn't be adapted to use dao3.6 or whatever A2k uses.

If you've any questions, ask away.

B ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------

 

[fredk]

Thanks for you help on this.

Ben, that makes my head spin! I am going to try it and see what happens. I am ok with the function, I just put that in a module - But where exactly to I place the code in your thread? That is where I am confused.

I have not really dealt with anything on this scale yet. I am eager to learn and this is a good way for me to get my feet wet.

Thanks for all of your help!!!!!!!

Fred

 

[oharab]

Put all of my code from the faq in a plain text file, and save it in the same directory as your database with a .vbs extension.
Then use the fhandlefile function to call that file as you leave the database ie put

fhandlefile("C:\Database\CompactDb.vbs",1)

on the OnClose event of your last form to close, or behind a "Compact Me" button.
the code waits until you are out of the database and compacts it.

hope that solves it for you.

B ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------

 

[fredk]

Ben, thanks so much for helping me - I have not referred to objects outside of access - This is a big help for me.

Thanks again for taking the time!!!

Fred