[AccessD] Compact and Repair Database

You could do this a little easier.

1) Set your database to 'Compact on Close'
2) Put some startup code in your database that says, "If it's between 2:00
and 2:15, then Quit."
3) Create a Scheduled Task on the PC where the database is located which
opens that database at 2:00 each weekday (or everyday).

At 2:00 each morning the Scheduled Task will open the database, the database
will recognize what time it is and then shut itself down, and the database
will be automatically compacted when it does.

HTH!
Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
Sent: Tuesday, December 18, 2007 2: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