MarcGraff
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
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
Check this link
How to Compact Databases at a Scheduled Time
http://support.microsoft.com/?id=158937
How to Compact Databases at a Scheduled Time
http://support.microsoft.com/?id=158937
ASKER
The error is coming up on repair:
DBEngine.RepairDatabase conFilePath & conFileName
I believe the compact section is working.
- Marc
DBEngine.RepairDatabase conFilePath & conFileName
I believe the compact section is working.
- Marc
DBEngine.RepairDatabase conFilePath & conFileName
-----------^^^^^^^^^------
oops sorry never mind my last post
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Oh! thanks!
- Marc
- Marc
Something like this
c:\program files\microsoft office\office10\msaccess "\\ServerName\Sahredfolder
copy \\ServerName\SharedFolder\
exec