[AccessD] Compact and Repair Database

Kaup, Chester Chester_Kaup at kindermorgan.com
Tue Dec 18 14:57:03 CST 2007


I am looking for some ideas on how to compact and repair nightly (2:00 AM) a database that is closed. The compacted database needs to have the same name as the original one. I had thought about using windows scheduler somehow to do this. I can across the following code but access stops and must exit when it gets to the line. 

DBEngine.CompactDatabase FilePath & OriginalFile, FilePath & _ FileWithoutExtention & "Temp.mdb"

Here is all the code:

Public Function cmdCompact()
On Error GoTo Err_cmdCompact
Dim DateUnderscore As String
Dim ExecutionMacro As String

   Set DB = CurrentDb
   
   DateUnderscore = Month(Date) & "_" & Day(Date) & "_" & Year(Date)
   FilePath = Mid(DB.Name, 1, Len(DB.Name) - Len(Dir(DB.Name)))
   OriginalFile = "Sacroc.mdb"
   FileWithoutExtention = Left(OriginalFile, InStr(OriginalFile, ".") - 1)
   DoCmd.Hourglass True

TryAgain:
   'Compact the Back-End database to a temp file.
   DBEngine.CompactDatabase FilePath & OriginalFile, FilePath & _ FileWithoutExtention & "Temp.mdb"

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

   'Rename the current database as backup and rename the temp file to
   'the original file name.
   Name FilePath & OriginalFile As FilePath & FileWithoutExtention & ".bak"
   Name FilePath & FileWithoutExtention & "Temp.mdb" As FilePath & _ OriginalFile
   DoCmd.Hourglass False

Exit_cmdCompact:
   Exit Function

Err_cmdCompact:
   If Err.Number = 3356 Then
       Resume TryAgain
   ElseIf Err.Number = 3045 Then
       Resume TryAgain
   Else
       MsgBox Err.Number & ": " & Err.Description
       Resume Exit_cmdCompact
   End If

Chester Kaup
Engineering Technician
Kinder Morgan CO2 Company, LLP
Office (432) 688-3797
FAX (432) 688-3799

 
No trees were killed in the sending of this message. However a large number of electrons were terribly inconvenienced.





More information about the AccessD mailing list