UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
> Getting Ready To Backup An Access Database (97 upwards)    
Getting Ready To Backup An Access Database (97 upwards)


Following is an excerpt from my book called Real World Microsoft Access Database Protection and Security. This section gives an overview of what you need to do to ensure your database is properly backed up. You can find this rest of this sample chapter and other information from here

Garry Robinson


Backing Up Multi-User Databases

To back up an Access database correctly, every user must log off the database. If you back up a database when someone is using it, you risk saving the database in an unstable state. A user may have made changes to data and objects and not saved them, so that when you open the archived database, you may receive a message that states that the database is corrupt. You then will need to use the repair utility and, at best, only a small amount of information will be lost. Unfortunately, you will never be able to determine exactly what that was as any corrupted data is usually unrecoverable.

To ensure that the database is ready to be backed up, you must have exclusive access to the database. This condition does not apply to some of the exporting backup options discussed in this chapter, but it is a good idea nonetheless. One way to tell whether someone else is in the database is to check for the existence of a file with the same name as the database and an .LDB extension, which indicates an Access locking file. As long as you don’t see this file, you should be able to open the database in exclusive mode. There are exceptions, however, which I will explain.

After you have exclusive access to the database, you can copy the file or export the information from the database. Before I describe some different ways to back up your database and data, I will show you how you can find out whether your database is being used.

Checking Whether Anyone Has the Database Open

The first and simplest way to determine whether someone’s in the database is to check for an .LDB file with the same name as the database that you are using. You can check in Windows Explorer as follows:

  1. Open Windows Explorer and navigate to the folder that your database is in.
  2. Make sure that the display format of the folder is View Details or View List.
  3. Sort the files in the display by file name.
  4. Find the database and look for a file with the same name and the .LDB file extension.

The .LDB file is a good indicator of other people using the database, but sometimes a user turns off a computer or Windows crashes, and the .LDB file remains open. To cover for these contingencies, you can manually check whether you have exclusive access to a database by doing the following:

  1. Open Access.
  2. Choose File--> Open and navigate to the folder where your database is.
  3. Select the file, click the Open button’s drop-down arrow, and choose Open Exclusive.

If your database opens without any problem, then you can copy it to your backup media or compact it. If you are copying the file, you will need to close Access before doing the backup. Unfortunately, these manual processes are a little tedious, and you may want to automate the process a bit more.

Using VBA to Check Whether Anyone Is Using the Database

To find out whether someone is using a database, you will need to test whether you can open that database in exclusive mode. To try the demonstration form, open the sample database for the version of Access that you are interested in and choose Chapter 5 in the Demonstration Database Interface form. The first sample that I want to demonstrate is a form called frmIsDBopenDAO. Open this form in design mode because you may need to change the location of the Northwind database. The following code snippet demonstrates how you might use the IsDatabaseOpen function. If the (Northwind) database opens in exclusive mode, the function will return a True result.

' This form will test if it's possible to open a database exclusively.
Const MYDBPATH = "C:Program FilesMicrosoft OfficeOfficeSamplesnorthwind.mdb"
Dim myDbIsOpen As Boolean
myDbIsOpen = IsDatabaseOpen(MYDBPATH)
If myDbIsOpen Then
MsgBox "Database is already open or an error occurred."
MsgBox "Database is not being used by anyone."
End If

The logic used in the IsDatabaseOpen function commences by opening a DAO workspace object. By using that workspace object, we then attempt to open a database reference in exclusive mode. If the exclusive reference fails, it returns an error. We can then check the error number to see why we couldn’t open the database exclusively.

Function IsDatabaseOpen(strDbPath As String) As Boolean

' This function tests whether a database is open.
Const ALREADYOPEN = 3356

Dim wsp As DAO.Workspace
Dim myDbs As DAO.Database
On Error GoTo IsDatabaseOpen_error

' Returns reference to default workspace.
Set wsp = DBEngine.Workspaces(0)

' Attempts to open an exclusive reference to another database.
Set myDbs = wsp.OpenDatabase(strDbPath, True)

' No one is using the database.
IsDatabaseOpen = False
Set myDbs = Nothing
Set wsp = Nothing
Exit Function

' Test for errors, which are probably caused by trying to open the
' database in exclusive mode.

IsDatabaseOpen = True
Select Case Err.Number
     MsgBox Err.Description, vbInformation, "File Not Found"
   MsgBox Err.Description & vbCrLf & vbCrLf & strDbPath, _
     vbInformation, "Disk does not exist"

    ' Opened by one or more people. One name appears in message.
     MsgBox Err.Description, vbInformation, "File Already Open"
  Case ALREADYOPENEXCL ' Already opened exclusively by someone.
    MsgBox Err.Description, vbInformation, "File Already Opened Exclusively"
 Case Else
    MsgBox "Error number " & Err.Number & " -> " & Err.Description
End Select
GoTo IsDatabaseOpen_Exit
End Function

So now that we have examined how to find the "in-use" status of a database, let’s see how to ensure that everyone logs off the database in time for the backup.

Setting Automatic Shutdowns Before Scheduled Backups

Unless you run a business that uses an Access database around the clock, there comes a time in the day when it should be safe to close the database. To make this easy, I have created a form called frmAutoShutdown that will shut down your Access database at a set time. To add this form to your database, import it and add a line to your AutoExec macro to open the form in hidden mode. From then on, the form will sit quietly in the background and check every few minutes to see if it is time to shut down the database. Just prior to the shutdown time, the form will issue a warning message to users. When the shutdown time arrives, the form will save any open objects or forms and then close the database. Because this process logs everyone off during the night, it is very useful for administering the database in the early morning.

To understand how the frmAutoShutdown form works—and possibly to change the setup—let’s review the full form module that follows. The most important thing to be concerned with is the values of the module constants. Generally, you will be setting the SHUTDOWNHOUR constant to a time when everyone is tucked into bed and just before you run your scheduled backups. You will need to remember to modify that constant to an appropriate time when you are experimenting with the form. After that, you will see the warning messages and the actual shutdown constants. If you look at the form timer event, you will find that the MSGMINS constant decides the interval between triggers. When the shutdown hour arrives, the Quit method will shut down the database.

Option Explicit
' User administration constants
' Purpose: Shut down and user messages.
Const MSGMINS = 3 ' Minutes between checking for system shutdowns.
Const SHUTDOWNFLAG = True ' If True, then the system shuts down once a day.
Const SHUTDOWNHOUR = 0 ' Automatic shutdown hour (24-hour time).

Const WARNSTARTMINS = 0 ' Time in minutes that warnings start being issued.
Const WARNENDMINS = 10 ' Time in minutes that warnings stop being issued.
Const SHUTDOWNSTARTMINS = 10 ' Starting time in minutes for the shutdown.
Const SHUTDOWNENDMINS = 20 ' Final time in minutes for the shutdown.
Private Sub cmdOk_Click()
Me.visible = False
End Sub

Private Sub Form_Load()

' Always hide this form; the user shouldn't know that it is there.

 Me.visible = False
 Me.TimerInterval = MSGMINS * 1000 * 60#
End Sub

Private Sub Form_Timer()
 ' Shuts down the database (in case anyone has left the database open).
 Dim myDate, myDownTime, myUpTime, myMessage, minsDiff As Integer
 On Error GoTo Quick_Exit
   If Hour(Time()) = SHUTDOWNHOUR Then
     ' The time to shut down is nigh.
     If Minute(Time()) > SHUTDOWNSTARTMINS And Minute(Time()) < SHUTDOWNENDMINS Then
        ' Safely huts down the database , saving all open objects.
        Application.Quit acQuitSaveAll

   ElseIf Minute(Time()) > WARNSTARTMINS And Minute(Time()) < WARNENDMINS Then
      Me.visible = True
      lblMessage.Caption = "This database will close soon for administration."
      Me.Caption = "Please Stop What You Are Doing."
   End If
 End If
End If


End Sub

Now, if you use this form or something similar to shut down the open databases, you will be sure to produce a cleaner backup. Next, let me put my own ideas about conventional backups into the melting pot.

Normal Backups

Any database that you are involved with should be backed up to an alternative storage system such as a tape drive or CD-ROM. This backup should be systematic (routine—not something you only think about when a crisis strikes—and, ideally, scheduled) and have the following characteristics: Performed on a regular basis.

  • Kept off-site.
  • Stored on a good-quality storage medium, such as a backup tape, memory card, CD, DVD, or portable hard drive.
  • Kept in a secure and fireproof location.
  • Multiple copies on multiple mediums.

Naturally, your databases need not be the only files kept as part of the backup system. Under no circumstances should the backup be kept only on-site on a substandard medium like a floppy disk or saved by using backup system software and hardware that your supplier no longer supports.

Recovering the Backups

More important than the backup itself is regularly testing the recovery process. Make a note in your diary to test recovering your database at least once a month if personnel, software, or hardware changes and once a quarter when the process is stable. If a database becomes corrupt early in the working day, don’t try to repair the database. Instead, move that corrupted database to a safe area and restore the backup version from the night before. That way, you are really testing your backup processes.


The content of this Access Wiki article was migrated from the UtterAccess FAQA forum and cannot be edited at this time. If you are the original author and would like the article unlocked as a standard wiki article, which enables editing by others, or you are a reader and have identified errors that warrant the removal of this article, please notify the Access wiki moderators.

Original article by Gary Robinson

Custom Search

Thank you for your support!
This page has been accessed 6,120 times.  This page was last modified 00:38, 3 February 2012 by Jack Leach. Contributions by Alan Greenwood  Disclaimers