vb123.com

Garry Robinson's Popular MS Access, Office and VB Resource Site

 

Home  Contact Us

Order Software

Search vb123

 Smart Access  
The Magazine that Access Developers loved to read and write for is back
Article Index Here or
Purchase Here

RSS & Newsletter  
Join our XML/RSS Newsfeed or sign up for our informative newsletter on Office Automation, Access and VB topics
Sign up here

Get Good Help
If you need help with a database, our Australian Professionals could be the answer
Read More

123 For Office

Great shortcuts, supports Office 2013 to 2003, easy zipping.
Read More

  The Workbench  Find out who has your database open, start the correct version of Access, easy compacting and zip backups, change startup options,  compile, shutdown database
Read and Download


Access > SqlServer 
Upsize to SQL Server 2005 or 2008, easily repeated conversions, highly accurate SQL query
translation and web form conversion.
Read More

Like FMS Products?
Purchase them from us and get a free Workbench or Smart Access  More

Garry's Blog
Find out a few other things that Garry has been writing about Microsoft Access. Read more

About The Editor Garry Robinson writes for a number of popular computer magazines, is now a book author and has worked on 100+ Access databases. He is based in Sydney, Australia
Contact Us ...


 

 

Next Tip  Backing up Multi-User Databases

This is a sample from the Microsoft Access Protection book by Garry Robinson

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.
 

 




 

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:

  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 (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 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).

 


 

There is a ADO version of the IsDatabaseOpen function  in the download database.

Note:

TIP: An easier alternative is to use the backup option in the Access Workbench. This doesn't require any vba coding and will create a backup file as soon as everyone logs out of the database.

Sample Databases

  Click here for the Access 2002/2003 sample database file
  Click here for the Access 2000 sample database file
 

Else Click Here To Purchase if you don't own the Toolshed

Find Out More

These samples are discussed at length in Chapter 5 of Garry's Book on Access Protection and Security
Read More
You should also try out the simple backup process that comes with the Access Workbench

Other Pages At VB123.com That May Be Of Interest

How to Setup an Access Version Number System
Access Traps for the Nave Developer

Click on the following button Next Tip to jump to the next page in the protection samples loop.

 

Links >>>  Home | Search | Workbench | Orders | Newsletter | Access Security | Access professionals