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.
strFolder = GetBackupFolder()
strDateFileName = MakeFileName()
strBackupName = strFolder & strDateFileName & '.mdb'
If
Dir(strBackupName) = ''
Then
strDatabaseName = GetConfigItem('Databasename')
DBEngine.CompactDatabase strDatabaseName, strBackupName
MsgBox
'Database has been backed up to ' & _
vbCrLf & _
strBackupName
Else
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.
|
