Prevent users from opening an accde more than once

How can I prevent users from opening an accde more than once on a Windows PC?  Some users open it on each monitor and this is causing db corruption and other strangeness.

Thank in advance!
LVL 14
Bill RossProgrammerAsked:
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.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Bill RossProgrammerAuthor Commented:
Hi Jim,

Fantastic.  Since some users have 2 monitors I was thinking of putting 2 identical accde files on their workstations named differently llke db1.accde and db2.accde.  If they have db1 open then open db2.  If both open then switch to one or the other.  Do you think this will work by modifying your code?

Other ideas?

Thanks,   Bill
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Bill,

 That's doable.  They could open db1.accde, then db2.accde, and if they tried to open a second instance of either, they would be stopped.

For that, there's no real code modification required.  Rather it's simply a matter of modifying the attribute you mark the window with.  Following the example in the article and in this case , it might be "myApp1" and "myApp2".   The tags can be anything you want.   The only important thing is that every "app" is different.

If however your saying that you want them to be able to start two instances of db1.accde and not a third, then that would take some massaging of that code.   I don't think this is the case though from what your saying.

Jim.

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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Bill RossProgrammerAuthor Commented:
Hi Jim,

Perfect.  myApp - you're referring to the application title in Access, correct?  It will be a bit of a pain but I can title db1 and db2 differently then install them.  

Thanks for your help.

Bill
Bill RossProgrammerAuthor Commented:
Hi Jim,

One last question - do you suggest putting this code in the AutoExec macro or somewhere else?

Thanks again!

Bill
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I do it in the autoexec myself.

<<You're referring to the application title in Access, correct?  It will be a bit of a pain but I can title db1 and db2 differently then install them.  >>

  It can be anything you want....doesn't need to be the title.   And you don't need different code for DB1 and DB2.   You could modify the call to:

Public Function AppAlreadyUp(bAllowMultipleInstances As Boolean, bDisplayMsg As Boolean, strAppNamed as string) As Long

 and then do this in a procedure called from the Autoexec:

 ' Parse CurrentDB().Name to get the database name.

 If strName = "db1" then
      strApp = "myApp1"
 ElseIf strName = "db2" then
      strApp = "myApp2"
 End If

  lngRet = AppAlreadyUp(False, True, strApp)

 With this code, you'd make changes in DB1, then copy that and rename it DB2, then hand both to the user.   But it would be the same DB.

Jim.
Bill RossProgrammerAuthor Commented:
Hi Jim,

Got it working but can't figure out how to switch to start db2 if db1 is found but db2 isn't in use yet.

Thanks again!

Bill
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Bill,

 Not sure what your trying to do.....is db1 and db2 part of the same app?

Jim.
Bill RossProgrammerAuthor Commented:
Hi Jim,

App is SQL backend access fe.  Sometimes users have 2 monitors.  We have installer that creates desktop shortcut to accde.  What they have been doing is opening the shortcut 2x to get the db open twice, once on each monitor.  This creates problems.  Would like to open the other db (db1 or db2) when they click on shortcut of only one is open.  If both are open then stop them and go to db1.

Hope this is clear and thanks!

Bill
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
and the shortcut always points to db1?

Jim.
Bill RossProgrammerAuthor Commented:
Yes.  So if db1 is already open then db2 needs to open.  If db2 open then do nothing or go to db1.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Well you have everything required, but it will require a little refactoring.

 You can use the function below in your startup code to check first for db1 already running.  This is based on the code in the article.

 If this returns false for DB 1, then you just continue.
     If true, then check for DB 2.  
       If that is false, launch an instance of Access opening DB2
       If that is true, then you'll need to call AppAlreadyUp() for DB1.   That will kill this instance and switch you to DB1.

  Although if you do it that way, there is a tad extra overhead in checking for DB1 again, when you already know open.  So you could pull out the code to switch to an app and write a SwitchToApp().

  Actually now that I say that, I think I already have that.   Be back in a minute.

Jim.

 

Function CheckIfAppRunning(strAppName As String) As Integer

        ' Checks if the named app is currently running.
        ' NOTE: This checks for the app name being set as a
        '       MUTEX and thus only works when checking for
        '       another custom app.
        Const RoutineName = "CheckIfAppRunning"
        Const Version = "1.0"

        Dim lngMutexHandle As Long
        Dim lngHWnd As Long
        Dim lngReturn As Long

10      On Error GoTo CheckIfAppRunning_Error

        ' Create a muxtex object
20      lngMutexHandle = CreateMutex(0, 1, strAppName)

        ' Did we get a new instance or a handle to an existing one?
30      If Err.LastDllError = ERROR_ALREADY_EXISTS Then
          ' App is running
40        lngReturn = CloseHandle(lngMutexHandle)
50        CheckIfAppRunning = True
60      Else
70        CheckIfAppRunning = False
80      End If

CheckIfAppRunning_Exit:
90      On Error Resume Next

100     Exit Function

CheckIfAppRunning_Error:
110     UnexpectedError ModuleName, RoutineName, Version, Err.Number, Err.Description, Err.Source, VBA.Erl
120     CheckIfAppRunning = True
130     Resume CheckIfAppRunning_Exit

End Function

Open in new window

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I do have something close, but it's more than you need as it was for a slightly different task.  Someone on EE had an app that was split into two parts and they wanted a user to be able to switch back and forth between the two at will.  They also wanted the user to be able to start either app and call code from each other.  It was a tall order, but we got it done.

I'm posting the code anyway so you can look it over and see how I just re-arranged some of the pieces from the article.

But I would stick with just calling AppAlreadyUp()

Jim.

Public Sub SwitchToAppMarkedAs(strAppMarkedAs As String, strAppPathAndFileName As String)

  Dim bolFound As Boolean
  Dim lngHWND As Long
  Dim iid As GUID
  Dim obj As Object
  Dim lngReturn As Long

  ' Minimize the current window
  ShowWindow Application.hWndAccessApp, SW_SHOWMINIMIZED
  
  ' Find if the app is loaded, and if not, start it
  If AppIsLoaded(strAppMarkedAs) = False Then
    ' Start the application - we can use shell or automation.  I'll use automation
    Set oAccessRemoteApp = CreateObject("Access.Application")
    oAccessRemoteApp.UserControl = True
    oAccessRemoteApp.OpenCurrentDatabase (strAppPathAndFileName)

    ' Optionally run a macro or do something else here.
    ' oAccessRemoteApp.Run "<macro name>"
  End If

  ' Now switch to the app.
  ' Find the existing instance(it may have already been running) and switch to it
  bolFound = False

  Do While bolFound = False

    lngHWND = GetWindow(GetDesktopWindow(), GW_HWNDChild)

    Do While lngHWND > 0
      If GetProp(lngHWND, strAppMarkedAs) = 1 Then
         ' Make sure we have an object reference to it.
        If oAccessRemoteApp Is Nothing Then
          Call IIDFromString(StrPtr(IID_IDispatch), iid)
          If AccessibleObjectFromWindow(lngHWND, OBJID_NATIVEOM, iid, obj) = S_OK Then
            Set oAccessRemoteApp = obj
          End If
        End If
    
        BringWindowToTop (lngHWND)
        lngReturn = ShowWindow(lngHWND, 3)
        

        bolFound = True
        lngHWND = 0
      Else
        lngHWND = GetWindow(lngHWND, GW_HWNDNEXT)
      End If
    Loop

    DoEvents

  Loop

Exit_Procedure:

End Sub

Open in new window

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
BTW, that last procedure calls AppIsLoaded, which is below.

It's just a slightly different way of checking if a "marked" app is loaded.   instead of trying to create a Mutex, it looks for the window property.

Jim.

Public Function AppIsLoaded(strAppMarkedAs As String) As Boolean

      Dim lngHWND As Long

      AppIsLoaded = False

      lngHWND = GetWindow(GetDesktopWindow(), GW_HWNDChild)

      Do While lngHWND > 0
          If GetProp(lngHWND, strAppMarkedAs) = 1 Then
              AppIsLoaded = True
              lngHWND = 0
          Else
            lngHWND = GetWindow(lngHWND, GW_HWNDNEXT)
          End If
      Loop

End Function

Open in new window

Bill RossProgrammerAuthor Commented:
Hi Jim,

This is good stuff.  I have to scoot for a while.  Will test later today or tomorrow.

Thanks again!

Bill
Bill RossProgrammerAuthor Commented:
Hi Jim,

AppAlreadyUp seems to have an issue in that the first time it's run if the app is not up it creates a Mutex and then fails the next time it's run.  Seems we need to test the Mutex without creating one - not sure how to do that...

Maybe we can just test to see if laccdb exists???

Thoughts?

Thanks,

Bill
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Seems we need to test the Mutex without creating one - not sure how to do that...

 You can use CheckIfAppRunning() or AppIsLoaded().

 Look at this comment here:

https://www.experts-exchange.com/questions/29126752/Prevent-users-from-opening-an-accde-more-than-once.html#a42738766

 for the logic.

Jim.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
actually, give me a minute and I'll see if I can't make this clearer.

Jim.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
If CheckIfAppRunning("db1") = True then
   ' db1 is already running....is db2 running?
   If CheckIfAppRunning("db2") = False then
       ' Start db2
       ' Maybe switch to it?
   End If
   ' Now quit because db1 is already running
   Application.Quit
Else
   ' No instances running of db1, so call AppAlreadyUp() to create a mutex and mark the window
   lngReturn = AppAlreadyUp(false,false , "db1")
End If
Bill RossProgrammerAuthor Commented:
Hi Jim,

OK.  Sounds like it should work.  I'll test later today or Friday.  I really appreciate the help!  Have a good Thanksgiving.

Bill
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
ave a good Thanksgiving.

 You too!

 and keep in mind that you have all the pieces...just might need to re-arrange /refactor a bit.

Jim.
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.