Backup, do you trust the users or system administrators?

When the user exits the FE attempt to rename the backend MDB preferably with today's date in the name in yyyy-mm-dd format. Ensure you close all bound forms, including hidden forms, and reports before doing this. If you get an error message, oops, its busy so don't bother. If it is successful then compact it back.

You can use the following code to ensure all forms and reports are closed.  Note though that this doesn't work for any recordset or database variables you might have open.  Especially any you may use for performance reasons.   Note that the Forms and Reports collection are the open forms and reports and not all the forms and reports.

' close all other forms and reports
For Each frm In Forms
DoCmd.Close acForm, frm.Name
Next frm

For Each rpt In Reports
DoCmd.Close acReport, rpt.Name
Next rpt

See the DBEngine.CompactDatabase method in VBA help for more details.

I'm a paranoid pessimist. That's one of my fundamental principles. <smile> Therefore I always rename first and then compact back.

(And yes I do have enemies. Why do you ask? <smile>)

Furthermore I rename the file to something like data_2001-02-17.mdb. I then compact that one back. I then have a "garbage collection" routine which makes sure that only, say 10, days back of this file exists. I plan on modifying this routine to keep some weekly and monthly backups, maybe five of each, as well.  I also don't tell the users I've made such a backup hidden away.   Otherwise they'll start depending on it.

Most of my clients where I run this have quite small backends, i.e. 1 or 3 MB so this is quite fast. The client with the now 300 Mb backend (95 Mb when I wrote this article) would likely complain so I didn't implement that one there. What I was going to do was put some logic in so the first person in a new day would do the rename/compact routine. Rather than at the end of the day when everyone wants to go home.

Now the above method won't help where the users hard drive is lost.   If you're dealing in a networked environment you may want to consider making a copy of the backend on a users hard drive in addition to being on the server.  However the data may be considered confidential and not suitable for such a purpose.

This method also won't help in the event of a true disaster such as fire, tornado, whatever as it is quite likely the entire building is no longer available.  This isn't your, the programmers, responsibility.  The users management must ensure daily offsite backups of data are happening.

[ Access Tips | Access | Main ]

   Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Granite Fleet Manager - the best designed fleet maintenance tracking and management system available