Auto compact & repair back end

[John]

Hi

I have an access front-end/backend app ie backend is not directly opened by
the users. How can I set the backend db to automatically compact & repair?

Many Thanks

Regards

 

[Tom Wickerath]

I recommend that you not attempt to do so, at least not without making sure
that you have a back-up copy first. Plus, you would have to have exclusive
access to the file in order to do a compact and repair. That means no other
users are in the database.

I just helped a friend last week recover his database from an AOIndex error,
which he received after compacting over a network. He had not backed-up the
database first, and was in quite a panic.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom van Stiphout]

On Tue, 22 Jul 2008 05:17:08 +0100, "John" <[email protected]>
wrote:

ON THE SERVER you can schedule a task (Control Panel > Scheduled
Tasks) to execute a command line like this:
<path_to>msaccess.exe <path_to>your.mdb /compact
The Repair step is automatic with modern versions of Access.
You don't have to install full Access on the server machine; the
runtime version (a free download of A2007) will suffice.

-Tom.
Microsoft Access MVP

 

[Tom Wickerath]

Hi Tom.

I don't know of any network Admins who would allow one to install a copy of
msaccess.exe, even the run-time only mode, on their networks...

John:
If you really want this capability, then you might want to look into
obtaining a copy of Total Visual Agent from FMS. This software can run as a
Windows service, so that a user does not have to be logged on to the computer:

http://www.fmsinc.com/MicrosoftAccess/DatabaseCompact.html


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom van Stiphout]

I know of some, but I agree the requirement to be logged in is a
problematic one.
Once the DAO libraries are on the system, a bit of smart code can
execute the CompactDatabase method, including from a service. Perhaps
that's exactly what FMS is doing.

-Tom.

 

[Klatuu]

The process doesn't have to be on a server to be run from Windows Scheduler.
I have an app that runs from my computer every day at noon. It doesn't do
compacts, but it could.
Because it is done late Friday night, I have one that runs from our
development server that compacts and repairs about 500 backend mdbs that
reside on the production server. It doesn't compact them all every week. It
only compacts those that have not been compacted in the last 60 days.

 

[Tom Wickerath]

Hi Dave,

If you are compacting any .mdb file over the network, then you're braver
than me. Access (JET) is so suseptable to corruption from bad writes, which
can easily happen from network noise. Sure, you may get away with it for 300
times, but the day will come when it could be a real problem, especially if
you did not have a backup copy. As I mentioned in an earlier post, I just
helped a friend of mine last week recover from an AOIndex error that was the
result of his initiating a compact and repair from his desktop PC on the BE
file in a shared folder. Dirk Goldgar was very instrumental in helping
recover this database for my friend.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Klatuu]

Actually, I just went back and reviewed the process. It is running on the
production server, sorry about that. I forgot we had it moved to production
once it was completely tested.

I agree, but even if we had to run it from another server or a client, it
would be worth the risk. Backups are done daily, and there are 500 to do. A
manual process would be a bit unmanagable.

I should have remembered that move, it took a couple of days working with
Network Control and an onsite visit (yes I, a lowly developer was actually
allowed in the temple) to determine the dummy user they had set up did not
have access to the Office directories.

 

[David W. Fenton]

Once the DAO libraries are on the system, a bit of smart code can
execute the CompactDatabase method, including from a service.
Perhaps that's exactly what FMS is doing.

It ought to be doable with a VBScript, actually.

 

[Larry Daugherty]

Dave,

I agree with you that running from a PC is viable. [Running from the
hosting server is more reliable in terms of fewer in-process
failures].

If the process begins with a backup of the current Back End when
exclusive access to it can be gained and if all of the individual
commands in the process are logged with success or failure noted and
if the individual source file processing stops on any failed command
And the copy of the compacted Back End to it's original location is
the last step of the process then where is the risk to the installed
Back End any greater than the risk in a simple file copy?

HTH

 

[Tom Wickerath]

Hi Larry,

Someone like Peter Miller would be in a better position to answer your
question. Here is an old thread from 2002 that I bookmarked. Read what Peter
has to say in message #6, message #11 (especially paragraph 3)

http://groups.google.com/group/micr..._frm/thread/93e2fe8b9c382b23/06464a8836cfec11


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Larry Daugherty]

Thanks Tom but it wasn't a question but an assertion expressed as a
question to Dave (or other responder) inviting agreement or rebuttal.

Thanks for the Peter Miller link. I checked it out and have already
addressed all of the concerns he expressed. In the case of validating
the backup, it is a copy of the Compacted and Repaired database that
is copied back into the original location. I've been doing it that
way since I developed the process in the '90s. Thus the validation
occurs on the first use of the target database following the backup.
No explicit test process on a backup copy is necessary.

--
-Larry-
--

Hi Larry,

Someone like Peter Miller would be in a better position to answer your
question. Here is an old thread from 2002 that I bookmarked. Read what Peter
has to say in message #6, message #11 (especially paragraph 3)

http://groups.google.com/group/micr..._frm/thread/93e2fe8b9c382b23/06464a8836cfec11


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Dave,

I agree with you that running from a PC is viable. [Running from the
hosting server is more reliable in terms of fewer in-process
failures].

If the process begins with a backup of the current Back End when
exclusive access to it can be gained and if all of the individual
commands in the process are logged with success or failure noted and
if the individual source file processing stops on any failed command
And the copy of the compacted Back End to it's original location is
the last step of the process then where is the risk to the installed
Back End any greater than the risk in a simple file copy?

HTH