Compact and repair easy way

[John]

Hi

I need to give clients an easy way to compact and repair db when needed, be
it in the form of a desktop shortcut or a form with a button. I need to do
the following steps in one easily automated way;

1. Check that no user is using the database.

2. Either open as exclusive or rename file temporarily to avoid any user
opening db while this is in process.

3. Make a separate copy of the database (just in case and for history).

4. Do the compact & repair.

5. Make the db available to users, by renaming db file back to original or
removing the exclusive lock.

Any help to achieve this for end users with an easy to use interface would
be appreciated.

Thanks

Regards

 

[Jeff Boyce]

John

It sounds like you've decided that Compact & Repair is the 'solution' you
need. If you'll provide a bit more specific description about what problem
you are solving in this manner, folks here in the newsgroup may be able to
offer more specific suggestions...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John]

Hi Jeff

Very occasionally db goes corrupt and just to give clients a way to quickly
get up and running I need this feature.

Thanks

Regards

 

[Jeff Boyce]

I suspected a problem with corruption...

My situation may be different (I suspect it IS), but I have a pair of Access
apps that have been running and being used for the last 10 years without
corruption. I wonder if it would make more sense to prevent the corruption
you are seeing than offer a fix for the users after it happens?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John]

Jeff, it could be that one of the user's end tasked the app or restarted
server while someone had the app running but it is hard to control that...

Regards

 

[Jeff Boyce]

John

I agree ... work would be so much easier without users...<g>

Here's one approach that's worked for me:

I create the application itself, then a "loader" application. The "loader"
checks to see if there's a more recent version available. If there is, it
creates a backup copy of the application itself, downloads the newer
version, then launches it.

This is not a clean match for your situation, but I suspect you could follow
a similar logic. You could use a "loader" app that the users are actually
pointing at (via desktop shortcut), use that app to backup, then compact &
repair, then launch your main app.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

Hi Jeff

Very occasionally db goes corrupt and just to give clients a way to quickly
get up and running I need this feature.

Just to add to Jeff's concerns, you should be aware that there are types of
corruption which Compact and Repair does NOT handle. There is no substitute
for good, current backups!

For one thing, you should certainly have a split application, with the data in
a shared "backend" and with each user having their own copy of the "frontend".
Not only does this architecture prevent a lot of corruption in the first
place, but it makes it easy to deal with frontend corruption - since the
frontend doesn't contain any data, just delete it and replace it with a fresh
copy. Compacting the backend should be done as an administrative function (not
by a button click by a user unless you REALLY REALLY trust all your users!!),
and should include a backup step before the compact is run just in case the
compaction destroys the database (it can happen).

 

[a a r o n . k e m p f]

John;

if you want to talk about ARCHITECTURE, you should talk about SQL
SERVER.

Anyone using Jet is just asking for corruption problems.
SQL Server is free, and the development is _EASIER_ than jet

-Aaron