Compact Database Using Runtime?

[Wayne]

I'm trying to compact a database backend using the A2007 runtime. I'm
doing this by closing the frontend then automatically opening the
backend. When the backend opens I've set it up to automatically close
again. I've set the option in the backend to compact on close.

It all works well using the full version but the compact fails when
using the runtime giving an error that Access has stopped working. Is
it actually possible to compact with the runtime or is my only option
to install the full version?

[Gigamite]

Wayne wrote:
> I'm trying to compact a database backend using the A2007 runtime. I'm

> doing this by closing the frontend then automatically ...

... running a shortcut that compacts the backend. The target of the
shortcut would look like the following:

"C:\Program Files\Microsoft Office\Office12\msaccess.exe" /compact
"C:\Backend.accdb"

[paii, Ron]

"Wayne" <cqdi...@volcanomail.com> wrote in message
news:b4637631-85eb-4972...@i18g2000pro.googlegroups.com...

If your front-end is completely disconnected from the back-end, you can run
this from a module in the front-end. Otherwise add it to another MDB.

' Delete the copy of the backend made on a previous run of this function,
ignore error if file does not exist
Kill "BUBackend.mdb"

' Rename back-end file
Name "Backend.MDB As "BUBackend.mdb"

' Compact renamed back-end to original name
DBEngine.CompactDatabase "BUBackend.mdb", "Backend.MDB

' Verify the back-end exist, if not rename the backed-up copy to the
original back-end name

' Reopen the front-end

[Tony Toews [MVP]]

Wayne <cqdi...@volcanomail.com> wrote:

>I'm trying to compact a database backend using the A2007 runtime.

When the user exits the FE attempt to rename the backend MDB preferably with todays
date in the name in yyyy-mm-dd format. Ensure you close all bound forms, including
hidden forms, and reports before doing this. If you get an error message, oops, its
busy so don't bother. If it is successful then compact it back.

See my Backup, do you trust the users or sysadmins? tips page for more info.
http://www.granite.ab.ca/access/backup.htm

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Granite Fleet Manager http://www.granitefleet.com/

[Wayne]

Thanks Ron and Tony. I got it to work using your ideas. I'm using
the DBEngine.CompactDatabase method that works under the runtime. I'm
then renaming the file using today's date (I'm going to change this
sequence after reading the blurb on Tony's website), zipping the
renamed file with some code and a .dll which may have originally been
supplied by Tony, copying the zipped file to a backup folder, then
killing the renamed file and original zip.

The next thing I need to work out how to do is to limit the number of
zips stored in the backup folder. I've decided to do this every 3
days, so the number of zips will mount quickly.

The system I'm working on at the moment only has 2 users who sit next
to each other and prior to the compact a message is displayed asking
the user to ensure that no other users are connected. If an .ldb
exists the user is presented with another message telling them that
there is still a user connected.

What would be the best strategy to implement in a larger multiuser
environment to ensure that no users are connected to the backend prior
to compacting? Leaving a machine running all night and scheduling the
compact for the early hours of the morning is not always an option.

[Tony Toews [MVP]]

Wayne <cqdi...@volcanomail.com> wrote:

>The next thing I need to work out how to do is to limit the number of
>zips stored in the backup folder. I've decided to do this every 3
>days, so the number of zips will mount quickly.

I use the file API to couint the numer of files and kill the oldest.

>The system I'm working on at the moment only has 2 users who sit next
>to each other and prior to the compact a message is displayed asking
>the user to ensure that no other users are connected. If an .ldb
>exists the user is presented with another message telling them that
>there is still a user connected.

I just try to rename the BE. If it fails then someone is in it.

>What would be the best strategy to implement in a larger multiuser
>environment to ensure that no users are connected to the backend prior
>to compacting? Leaving a machine running all night and scheduling the
>compact for the early hours of the morning is not always an option.

HOW TO: Detect User Idle Time or Inactivity in Access 2000 (Q210297)
http://support.microsoft.com/?kbid=210297
ACC: How to Detect User Idle Time or Inactivity (Q128814)
http://support.microsoft.com/?kbid=128814

However we found that the code which runs on the timer event must be disabled for the
programmers. Otherwise weird things start happening when you're editing code.

Also print preview would sometimes not allow the users to run a menu item to export
the report to Excel or others. So you had to right click on the Previewed report to
get some type of internal focus back on the report so they could then export it.
This was also helped by extending the timer to five minutes.

The downside to extending the timer to five minutes was if a person stays in the same
form and at the same control for considerable parts of the day, ie someone doing the
same inquiries, the routine didn't realize that they had actually done something.
I'll be putting in some logic sometime to reset this timer whenever they do something
in the program.