Compact and Repair Access 2007 Through VBA Code

[kdbailey]

I am looking for a way to compact and repair database through vba. On Access 2003 I was able to "click" by navigating the toolbars through code. I do not believe that is an option anymore. Does anybody know how to do this?

I also have a quick access button setup for compact and repair if there a way to hit the quick access button through code.

[June7]

I Binged 'access 2007 vba compact and repair' but didn't find much.

http://www.access-programmers.co.uk/...d.php?t=160025

[rpeare]

I take it you don't want to compact the database every time you close it but just sort of an 'on demand' type process?

If you do want to compact it every time it's closes you can set that through your options.

If you want it to be on demand you can look here:

http://rogersaccessblog.blogspot.com...nt-access.html

[kdbailey]

I Binged 'access 2007 vba compact and repair' but didn't find much.

http://www.access-programmers.co.uk/...d.php?t=160025

Same, I looked about a month ago but basically found a lot of threads asking for a solution with not many answers.

[kdbailey]

I take it you don't want to compact the database every time you close it but just sort of an 'on demand' type process?

If you do want to compact it every time it's closes you can set that through your options.

If you want it to be on demand you can look here:

http://rogersaccessblog.blogspot.com...nt-access.html

Ya, I am looking for a more on demand type deal. I do a nightly update of all of our databases and am looking for the code to compact following the updates. Apparently this blog is blocked here at work, so I'll have to take a look when I get home.

[rpeare]

If you're looking to do a nightly update that is independent of someone clicking a button that's fairly easy.

You'd just have to have an access database (unrelated to any of your live databases) with an main form. in the ON OPEN event of that form you can have it run code that will compact and repair any number of external databases then close itself.

Let's say you have a database 'LiveDatabase.accdb' or '.mdb' that you want compacted nightly.

On your server (or other workstation that is left on) you can have a scheduled task to open your database that performs the maintenance. Let's call that database 'maintenance.accdb' or '.mdb'

You schedule a task on the host computer to pen maintenance.accdb every night at 10pm (assuming it's a regular 9-5 office and nobody will be in the database at that time)
when maintenance.accdb is opened it compacts and repairs LiveDatabase.accdb to the filename LiveDatabase-Updated.accdb (or whatever name you specify)
Copies the LiveDatabase.accdb to a 'backup' folder and removes the original copy from the working directory
Renames LiveDatabase-Updated.accdb to LiveDatabase.accdb
part of the code can also be to close maintenance.accdb when all the code is done running.

Just be warned, I don't believe the compact/repair will work if people remain in the application when they go home so you'd either have to terminate connections to the database programmatically (if they exist) or have a way to kick people off.

[naeemahmad]

Regarding users in the db during compact/repair..

When you compact a Microsoft Access file located on a volume that uses the NTFS file system, Access removes the existing file and replaces it with the compacted file.
It then applies the default file permissions to the new file. If the file is an Access database, use Access user-level security instead of file-level permissions.
Otherwise, use folder permissions. For more information about the NTFS file system and folder permissions, see Windows Help.


You must have Open/Run and Open Exclusive permissions for an Access database in order to compact and repair it, therefore 'rpeare' is correct, you should not have any users in the sysyem during this process.

http://office.microsoft.com/en-us/ac...010341740.aspx

Naeem~

[kdbailey]

I appreciate the input, I actually have figured out how to accomplish what I was looking for.

As far as ensuring people are out of the database, I wrote a simple, easy-to-understand function that kicks people out for inactivity for any specified amount of time. If anybody is interested in that code I can post it.