asked on

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?

Dale Fye

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.Edit
    rs!ProcessACompleted = Date()
    rs.Update

    '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.Edit
        rs!ProcessACompleted = dtPrevDate
        rs.Update
    End If
     
end if

ProcExit:
    On Error Resume Next
    rs.close
    set rs = nothing
    set db = nothing
    Exit Sub

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

Open in new window

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott McDaniel (EE MVE )

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:

http://www.fmsinc.com/MicrosoftAccess/monitor/database-users.htm

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 Paul

ASKER

Guys you are great!
Jim's answer is spot on, but the other two answers are so valuable.