Stop or Check multiple instances of Access on startup

Prevent users from opening multiple instances of access

Quite often we want to only allow the user to open the frontend file one and ONLY one time. It could be that some functionality simple doesn’t work if the frontend is opened multiple times on the same PC. Access can sometimes handle it, and sometimes not. Therefore the recommended approach is to have each user have his own copy of the frontend. But that doesn’t help if the user is opening it multiple times!

 

This problem has been around for ages, and Graham Mandeno came up with a solution which is posted on the Access MVP site: Check Multiple Instances of Access, but sadly that doesn’t work in Access 2007 or newer, because of some changes to the way the Access windows are structured.  So I came up with the code you can see below:

How to use it

Now while I was testing this I noticed a few things. First of all, it seems that as long as the frontend file is a accdb, that it cannot be opened more than once. If I try to open it again, access automatically set focus to the all ready opened accdb file. Atleast that is the behavior I am seeing in Access 2010 on my machine. So in that case, the code isn’t really required. But when I compile the frontend to a accde, its perfectly possible to open the frontend many times, in seperate accesss windows. But with the code above, we can now detect if thats the case, set focus to the all ready open/active application and quit the latest instance being opened.

 

There is a small thing to notice. The code seems to only work if the database is “fully open”. To overcome this, I placed a splash screen which opens first, added a timer to the splash screen, and at the end of the timer interval, I run the code, and if no other apps are open, I proceed to close the splash screen, and open my login form.

 

I’ve added both an accdb file (Which should be 2007 compatible, but I haven’t tested) and a demo accde file, which is only 2010/2013 compatible.

Download: OnlyOpenOnce

 

Posted in Misc., Random Tidbits
4 comments on “Stop or Check multiple instances of Access on startup
  1. Dave says:

    couple of things
    1.you can actually open 2 instances of accdb. if you double click within file manager you’re right – you can only open it once. but if you have an instant open already and then go into access you can open a second instance from there
    2.not clear where I should copy your code.I have it in a module and have a call to CheckMultipleInstances in the autoexec but this fails as CheckMultipleInstances is not a function. If I change it to a function I get a compile error on Me.Application.hWndAccessApp. Think I’m missing something here. Have you an extra form that is also needed?

    Ryetee

  2. TheSmileyCoder says:

    Hi Dave
    @1) I just tested, and you are quite right. But the code still works in those cases, so we are still covered. Phew 🙂

    @2) Just try to remove the ME. I added that at the last second, which seemed wise at the time, since I was using the code from a form. I had noticed that the GetObject would sometimes start a new app, if I ran the CheckMultipleInstances as the first thing in my startup. So what I did was add a splash screen (See download), and run the code from there, after a few seconds. It could be there are other ways to resolve this, such as the use of DoEvents, but I never really looked into it further, since I like the splashscreen. Obviously my “real” splash screen is a tad prettier than the one I stuffed in the demo 🙂

  3. Dave says:

    Taking ME out sort of works!! I get the error message but when it executes the quit command I get a an error message saying I can’t quit “MyDB Name” with the text “If you’re running a Visual Back module that is using OLE or DDE, you may need to interrupt the module”.

    I’ve no idea what that means and if I am or not so back to google to work it out! I’m nearly there!!

    Ryetee

  4. Dave says:

    Changed your docmd.quit to application.quit and all seems to work. I’ll give it s bit of a hammering now to make sure.

    Ryetee

Ask a question, leave some feedback, post a reply!