PC Review


Reply
Thread Tools Rate Thread

auto - compact repair

 
 
fishqqq@hotmail.com
Guest
Posts: n/a
 
      5th Nov 2010
We have back end data on a server that only has admin access that
needs to be compacted/repaired about once per month. Is there a way to
set this up automatically - say at 2am when the server is quiet?

Right now we have to find someone with admin access who can do it and
until we do all the wkstns are down.

Thank you
Steve
 
Reply With Quote
 
 
 
 
David-W-Fenton
Guest
Posts: n/a
 
      5th Nov 2010
"(E-Mail Removed)" <(E-Mail Removed)> wrote in
news:(E-Mail Removed)
m:

> We have back end data on a server that only has admin access that
> needs to be compacted/repaired about once per month. Is there a
> way to set this up automatically - say at 2am when the server is
> quiet?
>
> Right now we have to find someone with admin access who can do it
> and until we do all the wkstns are down.


I use a VBScript that I schedule in the Windows Scheduler. After my
signature is one recent one. The comments at the top explain how to
set it up. The database names and paths are hardwired into the
script, instead of it being generic and called with parameters.

This particular version is fairly elaborate, with logging, and a
check for compact errors.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

Dim strDBName
Dim strDBPath
Dim strSourceDB
Dim strBackupDB
Dim objFSO
Dim objEngine
Dim objDB
Dim tdf

' change this to the name of the main data file
' without the extension
strDBName = "Data 2003"
' change this to the path to where the data file
' is with trailing backslash
strDBPath = "D:\PathToDataFile\"
' create a CompactBackup folder in that folder
' copy the CompactLog.txt file into this folder

strLogFile = strDBPath & "CompactBackup\CompactLog.txt"
strSourceDB = strDBPath & strDBName
strBackupDB = strDBPath & "CompactBackup\" & Year(Date()) & "-" _
& Right("00" & FormatNumber(Month(Date()), 0), 2) & "-" _
& Right("00" & FormatNumber(Day(Date()), 0), 2) _
& strDBName

Set objEngine = CreateObject("DAO.DBEngine.36")

Set objFSO = CreateObject("Scripting.FileSystemObject")
If (objFSO.FileExists(strSourceDB & ".ldb")) Then
Call WriteLog(Err, "Skipping compact because file is open", _
objFSO, strLogFile)
Else
If (objFSO.FileExists(strBackupDB & ".mdb")) Then
On Error Resume Next
objFSO.DeleteFile strBackupDB & ".mdb"
Call WriteLog(Err, "Deleting file", objFSO, strLogFile)
On Error GoTo 0
End If
On Error Resume Next
objFSO.MoveFile strSourceDB & ".mdb", strBackupDB & ".mdb"
Call WriteLog(Err, "Moving file", objFSO, strLogFile)
On Error GoTo 0

On Error Resume Next
objEngine.CompactDatabase strBackupDB & ".mdb", _
strSourceDB & ".mdb"
Call WriteLog(Err, "Compacting file", objFSO, strLogFile)
On Error GoTo 0

' check for Compact Errors table
Set objDB = objEngine.OpenDatabase(strSourceDB & ".mdb")
For Each tdf in objDB.TableDefs
If tdf.Name = "MSysCompactErrors" Then
On Error Resume Next
Err.Raise 9999, , "Errors occured in compact"
Call WriteLog(Err, "MSysCompactErrors table found", _
objFSO, strLogFile)
Err.Clear
End If
Next
Set tdf = Nothing
Set objDB = Nothing
End If 'LckFile

Set objEngine = Nothing

Private Sub WriteLog(ByRef objErr, ByVal strMessage, _
ByRef objFSO, ByVal strLogFile)
Dim strLogEntry
Dim objLogFile
Dim ts

If Err.Number = 0 Then
strLogEntry = Now() & " -- Success (" & strMessage & ")"
Else
strLogEntry = Now () & " -- " & Err.Number & ": " _
& Err.Description & " (" & strMessage & ")"
End If
'MsgBox strLogEntry
Set objLogFile = objFSO.GetFile(strLogFile)
Set ts = objLogFile.OpenAsTextStream(8)
ts.WriteLine strLogEntry
ts.Close
set ts = Nothing
Set objLogFile = Nothing
End Sub
 
Reply With Quote
 
 
 
 
fishqqq@hotmail.com
Guest
Posts: n/a
 
      7th Nov 2010
On Nov 5, 12:20*pm, "(E-Mail Removed)" <(E-Mail Removed)> wrote:
> We have back end data on a server that only has admin access that
> needs to be compacted/repaired about once per month. Is there a way to
> set this up automatically *- say at 2am when the server is quiet?
>
> Right now we have to find someone with admin access who can do it *and
> until we do all the wkstns are down.
>
> Thank you
> Steve


Thanks David, can anyone else offer a simpler approach - perhaps with
macros etc? This approach is well over my head
thanks
STeve
 
Reply With Quote
 
fishqqq@hotmail.com
Guest
Posts: n/a
 
      7th Nov 2010
On Nov 5, 12:20*pm, "(E-Mail Removed)" <(E-Mail Removed)> wrote:
> We have back end data on a server that only has admin access that
> needs to be compacted/repaired about once per month. Is there a way to
> set this up automatically *- say at 2am when the server is quiet?
>
> Right now we have to find someone with admin access who can do it *and
> until we do all the wkstns are down.
>
> Thank you
> Steve




I noticed in the Options / General tap there is a check box for
"compact on close" which will compact the MDB file after the user
closes it.
The files i need to auto compact are on the backend server and the
users never open then directly. They only open them through the front
end which is on their workstations. Can someone tell me if when all
the users are logged off (and the .ldb files on the backend are
closed) will this trigger the auto compact on close ? This would be
great as when all the users are logged off they would automatically
compact the back end data

tks
 
Reply With Quote
 
David-W-Fenton
Guest
Posts: n/a
 
      8th Nov 2010
"(E-Mail Removed)" <(E-Mail Removed)> wrote in
news:(E-Mail Removed)
m:

> I noticed in the Options / General tap there is a check box for
> "compact on close" which will compact the MDB file after the user
> closes it.


COMPACT ON CLOSE is useless in any properly distributed database,
which will have a front end and back end, and the only thing that
anyone other than the developer will ever open in the Access UI is
the front end, which doesn't need to be compacted.

Secondly, it's not just useless but actually HARMFUL and DANGEROUS,
as it can't be cancelled. In certain situations, a database can be
corrupted but still openable and the data still accessible, but in a
state that compacting will cause to lose data. It's happened to me
(before COMPACT ON CLOSE existed), and I fortunately had a backup,
so could get to the data in the backup, even though it had been lost
in the compacted file.

But COMPACT ON CLOSE gives you no option to make a backup or skip
the compact, so it's going to cause data loss if you ever encounter
that corrupted state.

There is simply no circumstance where anyone should ever turn on
COMPACT ON CLOSE. It's a badly-implemented feature that never should
have been released in this form by MS.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto compact & repair back end John Microsoft Access 11 23rd Jul 2008 11:57 AM
Compact an Access Applicaton by checking Compact on Close tomlebold@msn.com Microsoft Access 2 15th Jan 2007 08:00 PM
.net compact 2.0 VS .net compact 1.1( compatiblity) =?Utf-8?B?c2FtZWVy?= Microsoft Dot NET Compact Framework 2 28th Oct 2006 03:23 AM
Stopping Compact and Repair (and Compact on Close) - Records Disappear gee664@gmail.com Microsoft Access 7 25th Apr 2006 05:17 PM
is there any auto compact repair utility to purchase ? =?Utf-8?B?U3VyYWo=?= Microsoft Access 3 17th Oct 2005 05:55 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:53 AM.