automating a nightly safe backup & compact

[bicyclops]

I've read alot of the posts about backup. Many mention FileCopy or a batch
file; and that this is safe 99% of the time. But it would be good to have
100%. The Tools-Backup Database feature is great because it ensures that no
users have the db open first; otherwise it doesn't do the bakcup. I've had
poor success trying to automatic this. The process usually (but not always)
gets stuck when the dialog asks for a filename.

I also have a process for shutting down all FE users that works MOST of the
time, except when someone leaves a prompt or dialog on their screen. So I can
never guarantee it will work. So imagine this scenario:

1. Shutdown routine starts but fails because user left db open with dialog
on screen.
2. FileCopy copies BE database to another location, but this may not be a
valid file because FE instances were open.
3. Compact routine runs and results in lost data because of prior corruption.
4. Both original AND backup copies are now unusable.

To avoid this scenario, there must be a way to at least verify that the
database is open exclusively, otherwise the whole process should not execute.
There most be a way to check for this . . .

Thanks in advance.

 

[Phil Hunt]

after you copy it, you should try to compact it. If it compact, it's good
and replace the prodcution version with it.

 

[bicyclops]

Phil- Are you sure about that? I've read that compacting a database that has
minor corruption can possibly result in lost data. The technique would be to
try to get rid of the corruption before compacting.

Therefore that is why I want to ensure a SAFE backup before compacting.

And since I'm trying to automate the process, I won't be around to check it
when it happens.

 

[Phil Hunt]

I don't think there is a sure thing in Access. That's why you see sso many
discussion on the topic.
Lost of data is often better than DB not able to open.
In one of application, I go as far as scheduling a window task to shut down
the PC at midnite when no one is around. User in this case is willing to
accept that.

 

[aaron.kempf]

use Access Data Projects; MDB is crap

with SQL Server; or even a developers' edition-- you can schedule a
backup against a MSDE production machine.

i mean-- WOW it is a better solution than piece of crap MDB backups

 

[bicyclops]

Fair enough; but I'm not willing to accept EITHER (lost data or a hosed db).
So that takes me back to my original question. Does anybody know how to
programatically verify that Access is open in (or can be switched to)
Exclusive mode?

 

[Phil Hunt]

If you can compact it, it must have been opened exclusively.
You can try to open the mdb by opening it with the option of "exclusively",
i think it is the second paremeter on the open statement. For me, I just
compact it, if it cannot compact, it is opened somewhere by someone.

 

[bicyclops]

This is a cart before the horse issue. I need to perform these steps in this
order:
1. Verify database is opened (or can be opened) in exclusive mode.
2. Back up the database
3. Compact the database.

I can't do 3 before 2 (or risk data loss), and I can't do 2 before 1 (or
risk data loss). And remember that what I'm trying to create is an automated
nightly process, not an interactive one.

 

[Phil Hunt]

ok, your your 1,2,3 make sense.
I just choose to do all 3 steps in 1 by compacting it regardless. I went
thru the same thinking process and realize it ends up with the same result.

 

[bicyclops]

Phil- Well, I appreciate your taking time to help out.

That said, my original question was never answered. It would be great if
anybody else (MVP's?) would care to comment on it.

Thanks!

 

[aaron.kempf]

bicyclops

you are gambling with your data by using MDB.

run away while you still can and use sql server for everything going
down the road.