Backing up Multi-User Databases | Real World Microsoft Access Database Protection and Security

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.

Tip	See the "Further Reading" section at the end of this chapter for a Web reference on database corruption.

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.

Note

Chapter 6 discusses, at great length, different ways to find a list of users who are logged onto the database and even how to stop them from logging on to the database. Understanding these processes is important if you want to be the only person to have access to a database at a particular time. The .LDB locking file is discussed further in that chapter.

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 :

Open Windows Explorer and navigate to the folder that your database is in.
Make sure that the display format of the folder is View Details or View List.
Sort the files in the display by file name.
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:

Open Access.
Choose File ˜ Open and navigate to the folder where your database is.
Select the file, click the Open button's drop-down arrow, and choose Open Exclusive (shown in Figure 5-1).

Figure 5-1: Testing whether you have exclusive access to a database.

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. To help with that, I have developed two equivalent functions that will tell you whether a database is being used.

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 Files\Microsoft Office\Office\Samples\northwind.mdb" Dim myDbIsOpen As Boolean myDbIsOpen = IsDatabaseOpen(MYDBPATH) If myDbIsOpen Then   MsgBox "Database is already open or an error occurred." Else   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 FILENOTFOUND = 3024 Const ALREADYOPEN = 3356 Const ALREADYOPENEXCL = 3045 Const DISKDOESNOTEXIST = 3043 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 IsDatabaseOpen_Exit:   Exit Function IsDatabaseOpen_error:   ' Test for errors, which are probably caused by trying to open the   ' database in exclusive mode.   IsDatabaseOpen = True   Select Case Err.Number     Case FILENOTFOUND       MsgBox Err.Description, vbInformation, "File Not Found"     Case DISKDOESNOTEXIST       MsgBox Err.Description & vbCrLf & vbCrLf & strDbPath, _        vbInformation, "Disk does not exist"     Case ALREADYOPEN       ' 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

When you try to use the IsDatabaseOpen function to open a database that someone is already using in shared mode, error number 3356 (signified by the constant ALREADYOPEN ) returns an interesting error description. Unfortunately, because someone is using the database in shared mode, this description incorrectly says, "You attempted to open a database that is already opened exclusively by user 'Admin' on machine 'MY COMPUTER.' Try again when the database is available." Alternatively, if you have opened the database in exclusive mode and then use the IsDatabaseOpen function, it will return a message that says, "The file is already in use" (and doesn't mention it being exclusive at all).

I find this interesting because if you use the IsDatabaseOpenADO function, you will find in the frmIsDBopenDAO demonstration form that the equivalent code to check for exclusive access using the ActiveX Data Objects (ADO) library, the error descriptions returned are the same. Remember before running the code in the form that you will need to open this form in design mode because you may need to change the location of the Northwind database. That means that ADO is using exactly the same DAO calls to open the database. In addition, you can see that ADO returns only one error number for all the three errors provided in the DAO example. You then have to parse the error descriptions that accompany the numbers to establish what has gone wrong with the ADO exclusive open.

 Function IsDBaseOpenADO(MyDBpath As String) ' This function tests whether a database is open ' by using the ADO Library. Const ALREADYOPEN = -2147467259 Dim cnnDB As ADODB.Connection On Error GoTo IsDBaseOpenADO_error    Set cnnDB = New ADODB.Connection    ' Open database for shared (by default), read/write access, and    ' specify database password.    With cnnDB      .Provider = "Microsoft.Jet.OLEDB.4.0"      .Mode = adModeShareExclusive      .Open MyDBpath    End With    IsDBaseOpenADO = False    cnnDB.Close    Set cnnDB = Nothing IsDBaseOpenADO_Exit:   Exit Function IsDBaseOpenADO_error:   IsDBaseOpenADO = True   Select Case Err.Number     Case ALREADYOPEN       ' Opened by one or more people. One name appears in message.       If InStr(Err.Description, "not a valid path") Then         MsgBox Err.Description, vbInformation, "File Not Found"       ElseIf InStr(Err.Description, "file already in use") Then         ' This database is opened exclusively.         MsgBox Err.Description, vbInformation, "File Open Exclusively"       Else         ' This database is opened by other users.         MsgBox Err.Description, vbInformation, "File Already Open"       End If     Case Else       MsgBox "Error number " & Err.Number & " -> " & Err.Description   End Select   GoTo IsDBaseOpenADO_Exit End Function

After working through both of these samples in detail, I prefer the DAO function to the ADO function because the DAO function returns better error numbers.

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 SHUTDOWNFLAG Then   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 Quick_Exit: 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.