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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
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

0
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
http://support.microsoft.com/en-us/kb/285822

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

Resource locking in your applications
http://www.experts-exchange.com/Database/MS_Access/A_5328-Resource-locking-in-your-applications.html

 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.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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:

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.
0
Fritz PaulAuthor Commented:
Guys you are great!
Jim's answer is spot on, but the other two answers are so valuable.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.