How to check if any user is logged in before backup ?

I am using a backup utility where in at the time of exiting the application, user is asked just to ensure that every user is logged out and than the db is copied to local computer of the user. Its only a message box, doing nothing except warning.. :)

Can you suggest just to check if any user is there already logged in so that backup process can be halted to avoid damaging db ?
Will be grateful for the answer.

Kamlesh JainManagerAsked:
Who is Participating?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Function CanOpenDbExclusively(strDBPath As String) As Boolean

        Dim dbe As PrivDBEngine
        Dim wrk As Workspace
        Dim dbs As Database

        Const conFileInUse = 3045
        Const conDBOpenedExclusively = 3356

10      On Error Resume Next

20      Set dbe = New PrivDBEngine
30      Set wrk = dbe.Workspaces(0)
40      Set dbs = wrk.OpenDatabase(strDBPath, True)

50      If dbs Is Nothing Then
          ' If error is unanticipated, display message.
60        If (Err = conFileInUse Or Err = conDBOpenedExclusively) Then
            ' If database cannot be opened exclusively, return False.
70          CanOpenDbExclusively = False
80        Else
90          MsgBox "Error: " & Err & ": " & vbCrLf & Err.Description
100       End If
110     Else
          ' If database can be opened exclusively, return True.
120       CanOpenDbExclusively = True
130       dbs.Close
140     End If

End Function

Open in new window

Dale FyeCommented:
Well, if you don't have any bound forms open, and don't have any global recordset or database objects open, then the BE file will not have a .laccdb file.  Generally, I do my backups from my splash screen, which loads before any bound forms or any other objects are created.  It is also the last form to close in my applications, so all other forms are closed at that point.  If the .laccdb file for the BE file still exists, then you know there is still someone else in the database.

Also check out this reference which uses the UserRoster
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can check for the existence of a lockfile before you backup. The lockfile is named <YourDatabaseName>.laccdb. If that file exists, then someone has it open.

Better still - use a real backup utility, like Windows Backup. You don't really have to worry about such things, since the backup system will take care of those.

As with any backup, you should periodically check the backups to be sure they are what you expect. Nothing worse than recovering from a disaster only to find the your backups are bad as well ...
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I would not use the ldb file as an indicator as it can be incorrect in some cases.   99% of the time it is accurate, but if someone Ctrl/Alt/Deletes out, or doesn't have delete priv for the directory, then it will remain.

 What you can do is attempt to open the DB exclusively.   If you can't do that, then someone else is in the DB.

 But as Scott said, you'd be better off with backup software that has an open file option and uses VSS to snapshot the file.  That might not give you a consistent DB though from a user viewpoint.

Kamlesh JainManagerAuthor Commented:
Dear Mr Jim,
Would be obliged if you could brief what exactly this function does ?
Will it allow copying (Backup) even if some one is already using this database ?
Or it will warn the user that some one is using the db ?
Kamlesh Jain
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  It lets you know if anyone else has the DB file open for any purpose.

  The function attempts to open the DB exclusively...if it can do that, then no one else is in the database.  If it cannot do that, then someone is still in the database.  So for a complete solution, you would:

1. Lock users out of the database using your own built-in method, or use the JET passive shutdown feature.   Using your own built-in method is generally better as it gives you more control.

2.  Ensure that everyone is out of the DB using the function I posted.

3. "backup" the DB by copying the file or using backup software.

4. Allow users back in to the DB.

 Doing the above steps gives you a copy of the database that is in a consistent state (not in the middle of an operation).

 If you just copy a DB file while it is in use, or even use backup software with an open file option, you may not get that.    For example, someone is in the middle of a month end close process and they have completed a couple of steps and are just finishing up.  You will get a backup, but it will be while they were in the middle of the process.  Typically however if do either of those in off-hours, your pretty safe.

 Following the steps above however is a a sure thing and you know you have a solid backup.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Jim's solution seems to be the most complete and robust
Kamlesh JainManagerAuthor Commented:
Dear Experts.
I am extremely sorry for not responding on time as i was on prolonged holidays. To be honest, i have not yet tested the above.
Shall revert asap.

Thanking you,
Kamlesh Jain
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  Not a problem.   Hope you enjoyed your time off.

  If you do have any problems in getting it working, just post back here again and we will get you going.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.