Access compact and repair via VBA 1

[emik]

Hi,

I searched the forums and found some threads, but none of them were able to come up with a solution. I wanted to refresh the topic and see if someone has discovered a way.

Thanks.

 

[emik]

I forgot to mention.

I have an older program that has a close button with this line of code:

CurrentDb.Properties("Auto Compact") = True

It does compact and repair, but on my current application I get the error that I cannot compact the open database while running a macro or vba.

 

[rjoubert]

Check out this link...

http://www.mvps.org/access/general/gen0041.htm

 

[emik]

I saw that link before, it does compact the database but it leaves it open. If I put a docmd.quit after that code I get an Access error saying I can't execute code (it starts compacting and then hits the quit command).

My main objective is when the user closes the database it will compact itself.

 

[rjoubert]

Tools - Options - General Tab. Check Compact on Close. If multiple users are sharing the same database, Access compacts the database when the last user closes it.

 

[emik]

Perfect! Thank you!

 

[mp9]

Alternatively, if you don't want all your users compacting on close (what if you have some users without administrative privileges - that could cause problems compacting) consider an admin tool like the Handy Access Launcher (

http://www22.brinkster.com/accessory/resources/001.shtml)

that enables you to schedule compacts, repairs and macro runs at regular intervals.