Compact an Access 2007 database

[bcooler]

Hello all!

I have created a few multi-user 2007 databases. I have always chosen the "Compact and Repair" option on close to prevent bloat. However, on occasion, the .accdb file will be missing, but a "database.mdb" will appear causing everyone's file links to quit working until I rename the file.

After a bunch of searching I found out the following:

1.) This happens when multiple people are using the database and someone tries to compact it on close, causing the command to fail.

2.) Some suggest not to do this because problems like a full hard drive and not being the only person in the database could cause corruption. Their suggestion is to manually compact routinely.

3.) I know that I'm too lazy to remember to compact all these databases with any regularity.

With that said, can someone point me to a solution that is bulletproof? I'm not sure how to execute, but would a command line combined with a Windows scheduled task get the job done (at low usage times like 3AM)? I found this code at

http://msdn.microsoft.com/en-us/library/bb220986(v=office.12).aspx

but have no idea how to use it and if it will work.

expression.CompactDatabase(SrcName, DstName, DstLocale, Options, password)

Thanks!

 

[kjv1611]

May be overkill, I didn't look very deeply into it, but perhaps you can find what you're looking for here, or else use this method instead:

http://www.ozgrid.com/forum/showthread.php?t=51272

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[MajP]

If this was employed correctly this would not be an issue. The backend is on the server holding only tables. Every user has a frontend with code, forms, reports, and queries. Any other employment is incorrect. All front-ends compact on close. Back end rarely if ever needs compacting and should be done by the administrator anyways not on close.

 

[bcooler]

Thanks for the courteous response. Have a great day.