Your independent source of Microsoft Office news, tips and advice since 1996
Updated Database Backup
Here’s an updated way for you to add backup capability to an Access database.
Access Archon #151
A few years ago, in Access Archon #115, I described a way to create backups of the current database, using several objects that you could import into a database, to make it easy to regularly save backup copies of the database. Over the years, I streamlined this method, paring it down to just a table, a module, and a set of macros to run the backup functions. Most recently, I updated the backup procedures for my upcoming book on Office 2007 (for this book, they will be part of an Access add-in that also includes the table and query field listing procedures that were featured in Access Archon #150).
Modifications to Backup Procedures
That book won’t be out for a while – it should be published around the time of the release of Office 2007 – but in the meanwhile I have prepared an Updated Backup database, with code for backing up both front-end (or standalone) databases and back-end databases. Unlike the older version of the backup code, there is no requirement for any specific syntax or location for the back end database; the path to the back end is picked up from the Current property of a linked table.
Another change: Since Access 2007 has a new database format, and a new extension (.accdb instead of .mdb), I had to modify the code to deal with either type of extension. The updated backup code will work with databases in formats from Access 2000 to Access 2007 (beta).
As with the earlier versions of the Backup technique, the dates and incrementing numbers for the backup databases are stored in a table, now called zstblBackupInfo (the zs prefix indicates that this is a system table). I added two extra fields, so that backup numbers could be incremented separately for the front-end and back-end databases. All the code is in the basBackup module; the two procedures that do the backups are called from the macros mcrBackupFrontEnd and mcrBackupBackEnd.
Using the Backup Procedures
If you want to add backup capability to an Access database, all you have to do is import zstblBackupInfo, basBackup, mcrBackupFrontEnd and (for databases with linked back ends), mcrBackupBackEnd into the database. Check that you have references to the DAO and Scripting Runtime libraries, compile the code, and you are ready to go. To back up the current database, run mcrBackupFrontEnd; to back up the back end, run mcrBackupBackEnd. The proposed save name for the database backup copy is presented in an InputBox, as shown in Figure A; you can edit the name as desired, say to indicate a milestone achieved.
Figure A. An InputBox that lets you modify the database copy name
The two procedures that back up a front-end (or standalone) database, or a back-end database, and the procedures that create incrementing numbers for them, are listed below:
The code in the sample database needs the following references (in addition to the default references):
Microsoft DAO 3.6 Object Library
Microsoft Scripting Runtime
If you import code or objects into a database of your own, you may need to set one or more of these references. The version number may differ, depending on your Office version; check the version you have. References are set in the References dialog, opened from the VBA window. For more information on working with references, see Access Archon #107, Working with References.
The zip file containing this article, in Word format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site. It is accarch151.zip, which is the last entry in the table of Access Archon columns for Access Watch.
Access 2000 database (can also be used in higher versions of Access)
Wherever you want
Office Watch has the latest news and tips about Microsoft Office.
Delivered once a week.
We never share your email address with anyone - never have, never will.