Convidad 18/12/2012, 09:50
Option Explicit
'------------------------------------------------------------------------------
'
'Este módulo irá compactar um banco de dados externo ou o banco de dados atual.
'Se o banco de dados atual está sendo compactado, que vai fechar o banco de dados,
'Criar um arquivo de lote, e reabrir.
'
'A inspiração por trás deste método vem de Bob Larson e do modo como seu
'Front-End Update" ferramenta funciona.
'
'As referências necessárias:
'1. Microsoft Scripting Runtime
'
'Para compactar um banco de dados, adicione o seguinte a um botão ou outro controle:
'
'CompactRepair Call (sSourceFile)
'Onde' sSourceFile 'é o caminho completo para a
'Banco de dados que você deseja compactar.
'
'Eu recomendo criar um backup antes de executar qualquer reparo / compact
'Operação, independentemente de qual método de diversos você está usando.
'
'No caso do formulário de inicialização do Form_Load ou sua rotina de inicialização certifique-se de
'Adicione o seguinte:
'Application.SetOption "Compact Auto", False
'Desta forma, o DB atual não compactar cada vez que se fecha.
'
'Eu acho que ter aprisionado o erro para o back-end em uso. Se houver
'São outros erros que precisam ser preso, por favor me avise para que eu possa
'Adicioná-los ao meu código.
'------------------------------------------------------------------------------
Dim FSO As FileSystemObject
Dim sDestFile As String
Dim sExt As String
Dim sFileName As String
Dim sPrompt As String
Dim sSourcePath As String
Dim sTitle As String
Public Function CompactRepair(ByVal sSourceFile As String) As Boolean
On Error GoTo Error_Handler
If sSourceFile <> Application.CurrentDb.Name Then
'Compact the back end
Set FSO = New FileSystemObject
sFileName = FSO.GetBaseName(sSourceFile)
sExt = "." & FSO.GetExtensionName(sSourceFile)
sSourcePath = FSO.GetParentFolderName(sSourceFile) & "\"
'Delete the previous Temp file if it exists.
If Dir(sSourcePath & sFileName & "_Temp" & sExt) <> "" Then
Kill sSourcePath & sFileName & "_Temp" & sExt
End If
'Compact the Back-End database to a temp file.
DBEngine.CompactDatabase sSourceFile, sSourcePath & sFileName & "_Temp" & sExt
'Delete the previous backup file if it exists.
If Dir(sSourcePath & sFileName & ".bak") <> "" Then
Kill sSourcePath & sFileName & ".bak"
End If
'Rename the current database as backup and rename the temp file to
'the original file name.
Name sSourceFile As sSourcePath & sFileName & ".bak"
Name sSourcePath & sFileName & "_Temp" & sExt As sSourceFile
Kill sSourcePath & sFileName & ".bak"
Set FSO = Nothing
Else
'Compact the front end
Application.SetOption "Auto Compact", True
'Sets the file name of the batch file to create
Dim BatchFile As String
BatchFile = CurrentProject.Path & "\Compact.cmd"
' creates the Batch file
' Change the ping value to allow for speed.
' 60000 = 60 secs, 30000 = 30secs etc
' I recommend 60 seconds to prevent any overlapping should the compact take time.
' Large databases may require more time and smaller databases may require less
' time. Just be sure you are not trying to open the database while it is still
' compacting.
Open BatchFile For Output As #1
Print #1, "Echo Off"
Print #1, "ECHO Compacting Front End"
Print #1, ""
Print #1, "ping 1.1.1.1 -n 1 -w 60000"
Print #1, ""
Print #1, "CLICK ANY KEY TO RESTART THE ACCESS PROGRAM"
Print #1, "START /I " & """MSAccess.exe"" " & sSourceFile
Print #1, ""
Print #1, "Del %0"
Close #1
' runs the batch file
Shell BatchFile
'closes the current front end and runs the batch file
DoCmd.Quit
End If
Error_Handler_Exit:
Exit Function
Error_Handler:
Select Case Err.Number
Case 3356
sPrompt = "The Back End is currently being used by another User. " & vbCrLf
sPrompt = sPrompt & "You can only Compact the Database if you are the only person using it." & vbCrLf
sPrompt = sPrompt & vbCrLf & "Please try again later."
sTitle = "Back End in use..."
MsgBox sPrompt, vbExclamation, sTitle
Err.Clear
Resume Error_Handler_Exit
Case Else
MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Database Error..."
Err.Clear
Resume Error_Handler_Exit
End Select
End Function