[AccessD] Compact and Repair Database

You can just schedule a batch job to run a 2 AM...

<Batch File Text>
@Echo off
if exist "X:\SomePath\SomeFile.ldb" del "X:\SomePath\SomeFile.ldb"
if exist "X:\SomePath\SomeFile.ldb" goto inuse

:compact
if not exist "X:\SomePath\SomeFile.mdb" goto NO_MDB
"C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE"
"X:\SomePath\SomeFile.mdb" /compact
Goto end
:inuse
echo SomeFile.mdb was in use. No Compact performed. >>
"Y:\PathToLogFile\Compact.log"
Goto end
:NO_MDB
echo X:\SomePath\SomeFile.mdb not found >> "Y:\PathToLogFile\Compact.log"
Goto end
:end
Exit



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
Sent: Tuesday, December 18, 2007 3:57 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Compact and Repair Database


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.


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com