auto - compact repair

  • Thread starter Thread starter fishqqq
  • Start date Start date
F

fishqqq

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
 
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.
Click to expand...

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
 
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
Click to expand...

Thanks David, can anyone else offer a simpler approach - perhaps with
macros etc? This approach is well over my head
thanks
STeve
 
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
Click to expand...



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
 
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.
Click to expand...

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.
 
Back
Top