auto - compact repair

Discussion in 'Microsoft Access Macros' started by fishqqq@hotmail.com, Nov 5, 2010.

  1. Guest

    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
     
    , Nov 5, 2010
    #1
    1. Advertisements

  2. "" <> wrote in
    news:
    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
     
    David-W-Fenton, Nov 5, 2010
    #2
    1. Advertisements

  3. Guest

    On Nov 5, 12:20 pm, "" <> 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
     
    , Nov 7, 2010
    #3
  4. Guest

    On Nov 5, 12:20 pm, "" <> 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
     
    , Nov 7, 2010
    #4
  5. "" <> wrote in
    news:
    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/
     
    David-W-Fenton, Nov 8, 2010
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. jack

    How can i compact & repair database with macros??

    jack, Aug 9, 2003, in forum: Microsoft Access Macros
    Replies:
    3
    Views:
    2,544
    m wark
    Oct 10, 2003
  2. Akua Aning

    Compact and Repair Database via Macro

    Akua Aning, Sep 3, 2003, in forum: Microsoft Access Macros
    Replies:
    3
    Views:
    1,586
    m wark
    Oct 10, 2003
  3. A.M

    Compact And Repair

    A.M, Sep 24, 2003, in forum: Microsoft Access Macros
    Replies:
    3
    Views:
    6,991
    Alick [MSFT]
    Sep 26, 2003
  4. A.M

    Compact And Repair Database

    A.M, Sep 24, 2003, in forum: Microsoft Access Macros
    Replies:
    5
    Views:
    537
    Hafeez Esmail
    Oct 24, 2003
  5. m wark

    Compact & Repair of an Access database via a macro

    m wark, Oct 10, 2003, in forum: Microsoft Access Macros
    Replies:
    1
    Views:
    14,368
    Dennis Schmidt
    Oct 10, 2003
Loading...

Share This Page