Back up an Access database in VBA code with DAO

If you have to write code to back up an Access database you may start thinking about creating a new Mdb file and then populating it with copies of the tables in the live database. There is a much easier way that's obvious once you know about it; just compact the database into a new file.

Compacting

The Visual Basic command to compact a database is:

DBEngine.CompactDatabase <olddatabasename>, <newdatabasename>

The following snippet checks whether today's backup has been done before using the CompactDatabase method to make a new backup.

'-- Make a datestamp filename like yyyymmdd.mdb from the system date
'-- and add it to the path of the backup folder.
strFolder = GetBackupFolder()
strDateFileName = MakeFileName()
strBackupName = strFolder & strDateFileName & '.mdb'

'-- Does today's backup exist?
If Dir(strBackupName) = '' Then
  '-- Read the database name from the Config table
  strDatabaseName = GetConfigItem('Databasename')
  DBEngine.CompactDatabase strDatabaseName, strBackupName
  MsgBox 'Database has been backed up to ' & _
         vbCrLf & _
         strBackupName
Else
  '-- Do nothing - Today's backup exists
End If

The code assumes that the Access system has been split into front-end and back-end databases. It reads the name of the data- holding database and the backup folder from a small Config table in the code database and calls MakeFileName to generate a filename such as 20041225.mdb from the year, month and day of the system date.

If this file exists then we know that today's backup has run already. If not, the program calls CompactDatabase to create the backup.

Warning

The backup is created as soon as the first person opens the database each morning. Compaction requires exclusive use of the database so there might be a conflict if a second person logs on before compaction is complete. That person will see a message that the database is already in use, a warning which might worry a user who was not expecting it.

We've not hit this situation yet on a 5 Mb database with four users but it will become more likely on a larger system. A post from Gary Condit suggests using a disk file as a sentinel to avoid this problem. The backup routine should read a small text file before starting. If the file is empty then it should write "busy" into the file and save it. At the end of the backup routine, the file should be opened again and cleared to indicate that the database is available for shared use again.

If any other user tries to log on and finds that the file is not empty then the program can display a message asking the user to try again in five minutes. This technique has the advantage that the administrator can use a simple text editor to write the 'busy' text into this file and effectively prevent any new users from logging on.