Link to home
Start Free TrialLog in
Avatar of MarcGraff
MarcGraffFlag for United States of America

asked on

Compact and Repair using VB code?

I am trying to repair the MS Access DB using this code but can't ge by the error "Operation is not supported for this type of object"

The Code:
Sub CompactDB()
    Dim MsgBoxVal As String
    On Error GoTo CompactDB_Err
    Const conFilePath = "C:\MyDB\"
    Const conFileName = "Sys.mdb"
    Const conBackupName = "System.bak"

    ' Delete the previous backup file if it exists.
    If Dir(conFilePath & conBackupName) <> "" Then
        Kill conFilePath & conBackupName
    End If

    ' Rename the current database as backup and rename the temp file to ' the original file name.
    FileCopy conFilePath & conFileName, conFilePath & conBackupName
   
    ' Compact the database to a temp file.
    DBEngine.CompactDatabase conFilePath & conFileName, conFilePath & "Tmp.mdb"
    MsgBoxVal = "Compacting is complete" & vbCrLf
   
    ' Repair the database to a temp file.
    DBEngine.RepairDatabase conFilePath & conFileName
    MsgBoxVal = MsgBoxVal & "Repair is complete"
   
Exit_CompactDB:
    Kill conFilePath & "Tmp.mdb"
    MsgBox MsgBoxVal
    Exit Sub

CompactDB_Err:
    MsgBoxVal = MsgBoxVal & Err.Description
    Resume Exit_CompactDB
    GoTo Exit_CompactDB
End Sub

   Thanks
   - Marc
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Would you like to do this using a bat file?
Something like this
c:\program files\microsoft office\office10\msaccess "\\ServerName\Sahredfolder\XXX.mdb" /Compact
copy \\ServerName\SharedFolder\XXX.mdb \\DestinationPathServer\DestinationFolder\YYYY.mdb
exec
Check this link

How to Compact Databases at a Scheduled Time
http://support.microsoft.com/?id=158937
Avatar of MarcGraff

ASKER

The error is coming up on repair:

    DBEngine.RepairDatabase conFilePath & conFileName

I believe the compact section is working.

   - Marc

DBEngine.RepairDatabase conFilePath & conFileName
-----------^^^^^^^^^----------this is not a supported operation in VBA
oops sorry never mind my last post
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Oh! thanks!

   - Marc