How to know whether database is already opened by another user

I use Access 2010.
I would like some code to run every time the back end database is opened by the first user, but not if it is opened by subsequent users.
To explain another way: user A opens the front end database, say numbers.accdb and the back end database numbers_be.accdb is still closed, then the code runs (which will automatically imply that the back_end is activated). However if user B then opens the front end, the code does not run.
How can I determine whether a user is the first user to open the back end database?
Fritz PaulAsked:
Who is Participating?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You'll need to determine if anyone else is in the database either by using the JET Show User Roster function:

How to determine who is logged on to a database by using Microsoft Jet UserRoster in Access

or you can log each user in/out of the DB:

Resource locking in your applications

 by creating a "user lock" when they enter, and clearing that lock when they leave.

The roster function in this case would probably be better.

Dale FyeCommented:
Best way would be to store a value in a table in the backend.  I generally use a table (tbl_dbProperties) for this type of purpose.  It only contains 1 record and a number of useful fields.  In your case, the field might be "ProcessACompleted"

When you open the front-end, run this subroutine which would check to see whether the date stored in that field is todays date.  If not, then immediately write todays date to that field and then run your process.  It might look something like:

Private Sub ProcessOnceDaily

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim dtPrevDate as Date

On Error goto ProcError

set db = Currentdb
set rs = db.openrecordset("tbl_dbProperties", , dbfailonerror)

dtPrevDate = rs!ProcessACompleted
if DateValue(rs!ProcessACompleted) < Date() Then
    rs!ProcessACompleted = Date()

    'Call your process but make it a function so that you can pass a value back
    'to this step to indicate whether the process completed successfully or not.
    'If not, then reset the ProcessACompleted value
    if fnYourProcess() = False then
        rs!ProcessACompleted = dtPrevDate
    End If
end if

    On Error Resume Next
    set rs = nothing
    set db = nothing
    Exit Sub

    msgbox err.number & vbcrlf & err.description, , "Process once daily"
    Resume ProcExit
end sub

Open in new window

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
But do you want this to run ANYTIME no one has the database open, and a user opens it?

Or do you want this to run at certain intervals (like daily, or weekly)?

If the second, then Dale's code would take care of that.

If the first then ... I'm not sure how you'd do this. You could check for the lockfile, but of course as soon as you open the database Access will create the lockfile, so you'd always return a positive. You could use the JetRoster to determine who has the file open. Here's the FMS article that explains how to do that:

Essentially, you'd implement that code, and check to see if ONLY the current machine name is in that list. If so, then you can assume you're the "first" to open the database, and fire your code. If not, then you can assume someone else has already opened the database, and you don't need to fire the code.

However, if you could tell us more about exactly what needs to be done once-and-only-once, we might be able to come up with better suggestions.
Fritz PaulAuthor Commented:
Guys you are great!
Jim's answer is spot on, but the other two answers are so valuable.
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.