Nightly Compact Job

[Paul]

Hi,
For my Access 2000 application, I tried to compact the
mdb file everynight. What's the best way to do?
Currently I created another mdb file with "On Timer"
event, so at night (say 11:00 PM), it will run the command
DBEngine.CompactDatabase dest, source.
Most of the time it will work, but seems not very
stable (80% successful rate). Last night, it copied the
file but after compact the size reduce from 58 meg to 3.
When I tried to run the application this morning, the
error message was "not a database format". I don't know
what happen?
What's the right way to do the nightly compact job?
Thanks for your kindly advise.

 

[Brian]

Hi,
For my Access 2000 application, I tried to compact the
mdb file everynight. What's the best way to do?
Currently I created another mdb file with "On Timer"
event, so at night (say 11:00 PM), it will run the command
DBEngine.CompactDatabase dest, source.
Most of the time it will work, but seems not very
stable (80% successful rate). Last night, it copied the
file but after compact the size reduce from 58 meg to 3.
When I tried to run the application this morning, the
error message was "not a database format". I don't know
what happen?
What's the right way to do the nightly compact job?
Thanks for your kindly advise.

1. Use the Windows scheduler: it's what it's there for.
2. Create a batch that
(a) creates a backup copy of the file
(b) runs Access using the /compact switch.

The latest version of Jetcomp allegedly supports command-line switches, but
I couldn't get it to work.

 

[Mike Labosh]

What's the right way to do the nightly compact job?

I've done this before, and each time, the perfect solution was a tiny Visual
Basic (not VBA) utility that has a Sub Main() that does the
DBEngine.CompactDatabase. You could write one that either uses command line
arguments or checks a registry key or .ini file to get the filename or
directory to search for databases to compact. You have lots of freedom to
do it different ways.

Then you just schedule it. (Start -> Settings -> Control Panel -> Scheduled
Tasks)

--
Peace & happy computing,

Mike Labosh, MCSD

"It's 4:30 am. Do you know where your stack pointer is?"

 

[Roger Carlson]

For my money, the proper way to compact a database is 1) to compact it into
a temporary file (MyDatabaseTemp.mdb, 2) rename the original file to the
same name with a date added (i.e. MyDatabase.mdb -->
MyDatabase10_05_2004.mdb), 3) then rename the temporary file to the old file
name (MyDatabaseTemp.mdb --> MyDatabase.mdb). Something like this:

'==============================
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 = "CompactCurrentDB.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

End Function
'==============================

This way, you have a backup available if you have a compacting error. If
the compact is successful, you can delete the backup, but I prefer to do
that manually.

You can find this code on my website (www.rogersaccesslibrary.com) in a
small Access database sample called: "CompactDatabase2k.mdb"

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Ron Hinds]

1. Use the Windows scheduler: it's what it's there for.
2. Create a batch that
(a) creates a backup copy of the file
(b) runs Access using the /compact switch.

The latest version of Jetcomp allegedly supports command-line switches, but
I couldn't get it to work.

JETCOMP -src:src_database_name -dest:dest_database_name

in it's simplest form. To see all the otions, click Start | Run and type in:
(Path to JETCOMP)\jetcomp.exe -? and click OK. I've used this method but
ended up settling on the same solution Mike Labosh in this thread suggests,
i.e., a small VB app that I run via the Scheduler. You have more control,
including the ability to backup the DB and restore the backup if there is an
error.

 

[Brian]

JETCOMP -src:src_database_name -dest:dest_database_name

in it's simplest form. To see all the otions, click Start | Run and type in:
(Path to JETCOMP)\jetcomp.exe -? and click OK. I've used this method but
ended up settling on the same solution Mike Labosh in this thread suggests,
i.e., a small VB app that I run via the Scheduler. You have more control,
including the ability to backup the DB and restore the backup if there is an
error.

I spent an hour fiddling around with Jetcomp and the command line: no
errors, it just didn't DO anything!

It's easy to create a backup from a command file.