Access 2000/2003/2007 crashes while compacting a database

[דרור]

I have this problem with Access:

The objective is to compact the database once in a while. For this purpose I
added this code line:

ChangeProperty "Auto Compact", DB_Boolean, True

The line is the last line in the application's main form's Form_Unload.

What happens is that:
1/4-1/3 of the times Access compacts the database and exists nicely.
The other 2/3-3/4 of the times Access crashes and Windows presents an ugly
message to the user.

This happens with the same database on the same machine in an unpredictable
way. The applications works and then a crash and so on.

Any idea on what's going on here?

Dror

 

[דרור]

One more piece of information: this behavior in nothing new. It was
exhibited by Access 2000, 2003 and now 2007.

Dror

 

[Mark A. Sam]

If you go to options, there is an option to Compact on close. You should use
that.

God Bless,

Mark A. Sam

 

[דרור]

Mark:

The issue is that I do not want the users to wait for the compact each and
every time they exit the application. They can open and close it dozen of
time each day.

What I want to be able to do is to compact the application ever so often,
say, once a week. That's why I use VBA code:

If DateDiff("d", datLastCompact, Date) > 7 Then
....
End If

Dror

 

[tkelley via AccessMonster.com]

I'm not clear on whether it crashes upon EVERY attempt at a compact, such as
a manual compact ... or if a manual compact works fine, and it only crashes
when using that code.

 

[דרור]

It never crashed when I porformed manual compact on the mdb file.
I then generate an mde file from the mdb file and distribute the mde to the
users.

The application crashes (as described above) when the users use the mde file.

The application has over 1000 users with all types of OS/Access (Windows
2000 to Vista, Access 2003/2007). The work well/crash behavior happens on all
OS and on all Access versions (also happened on Access 2000 in tha past).
Dror