Results 1 to 11 of 11
  1. #1
    TimVB123 is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2022
    Posts
    4

    Do not open access front end if already open

    Hi all
    I work in a multi user environment. Each user has their own front end(s).
    I have a 'master switchboard' (MS) which is a front end only and has buttons to open other Access databases.
    The MS has a password form which hold the user's name
    The buttons use VBA to identify the front end and the user name, i.e. ensuring they only open their own unique front end version of the database. I do this using a string variable, i.e.

    Dim strEmpName As String
    strEmpName = Forms![frmpassword].Form![EmployeeName]

    and I open the unique database this way:
    appAcc.OpenCurrentDatabase ("Z:\InsVB\InsVB User Front Ends") & strEmpName & ("\InsVB Front.accdb")

    I haven't built in any code to stop the unique front end instance opening if it is already open. I don't want the user to accidentally open a second front end and then possibly encounter data entry conflict if the same record is opened twice.
    I'm hoping I can use something as simply as If, Then, Else to determine if the front end is open. If so send user a message and do not open again, if not then open it.
    I've bumped into code checking for a locked file, i.e. .laccdb but not sure if this is what I need to be using and if so how to apply it.
    Happy to let you see all the code if you think it will help.
    Thanks
    Tim

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    The lockfile should exist if already open?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    TimVB123 is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2022
    Posts
    4
    Quote Originally Posted by Welshgasman View Post
    The lockfile should exist if already open?
    The lockfile exists but doesn't stop a second front end opening, i.e. you could enter data on 2 front ends. I can use 2 of exactly the same unique user front ends at the same time. On testing just now if I jump between both and make changes the other instance will update/show the change so I guess it doesn't matter provided there is only 1 locked file. Is this what you're saying? Thanks.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by TimVB123 View Post
    The lockfile exists but doesn't stop a second front end opening, i.e. you could enter data on 2 front ends. I can use 2 of exactly the same unique user front ends at the same time. On testing just now if I jump between both and make changes the other instance will update/show the change so I guess it doesn't matter provided there is only 1 locked file. Is this what you're saying? Thanks.
    No. I am saying you check for the lockfile. If it exists you quit the app.? Or is it there as soon as you start your first instance?
    I suppose you could check the content.? If more than one user, then quit that app.?

    I used to check that the file being run was the accde file and from the correct location. If not I threw out a message and quit the app. Same sort of logic though.

    Edit: Mine appears to have Admin as well as my username, so perhaps more than two?
    Edit:Edit. I just tried to open another copy of a DB, and it told me that Admin had locked the DB. Tried another DB and that opens, but puts the same usernames in the lock file again.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I think what gasman is suggesting is that you test whether the frontend lockfile exists and if it does then exit the procedure to open a new front end.

    edit: Oops. never mind. Gasman spoke for himself.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by moke123 View Post
    I think what gasman is suggesting is that you test whether the frontend lockfile exists and if it does then exit the procedure to open a new front end.

    edit: Oops. never mind. Gasman spoke for himself.
    Yes, but then I realised that it would probably exist immediately due to the first instance.
    So checking the contents seems one way.?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't think it would be bullet proof but it might suffice. Sometimes lock files can persist in a network environment but I think that's an issue with the network and not Access.
    One could log in a user in a be table and cancel the opening if their login is already there, but that's not bullet proof either. Abnormal db shutdown can leave a login in place.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here is some code that I used a long time ago (meaning before 64 bit version existed) that checks if an instance is already open and switches to that from the current app (which is what I think you want to do, not just stop the opening).

    Again, it would probably need to be modified to work in a 64 bit Office installation.

    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    TimVB123 is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2022
    Posts
    4
    Sorry for delay in responding. I was off work then other stuff got in the way whilst I played catch up. Ive added then IF...THEN...ELSE towards the end of my code, as below, but this doesn't work. Any thoughts please?

    Code:
    Private Sub btnInsurance_Click()
     
     
    'OPEN THE USER'S OWN UNIQUE FRONT END FOR THE INSURANCE DATABASE
        
    '***   MINIMIZE CURRENT DATABASE (MSFront Database) to ensure the opening database (InsVB Front) is shown to the user   ***
        DoCmd.RunCommand acCmdAppMinimize
     
    '***   SET VARIABLES FOR ACCESS APPLICATION   ***
     
    'Set variable name and type
        Dim appAcc As Access.Application
       
    'Set variable value (open a new version of Access)
        Set appAcc = New Access.Application
     
    '***   SET VARIABLE FOR USER'S OWN UNIQUE FRONT END   ***
     
    'Set variable type - User name for front end
        Dim strEmpName As String
     
    'Set variable value - Pathname
    'Taken from user login name in Passwords form
        strEmpName = Forms![frmpassword].Form![EmployeeName]
     
     
        If Dir("Z:\InsVB\InsVB User Front Ends\") & strEmpName & ("\InsVB Front.laccdb") = " " Then
           
        '***   MAKE DATABASE VISIBLE   ***
            appAcc.Visible = True
           
        '***   LOCATE AND OPEN OTHER DATABASE   ***
           
            appAcc.OpenCurrentDatabase ("Z:\InsVB\InsVB User Front Ends\") & strEmpName & ("\InsVB Front.accdb")
       
        '***   DETERMINE WHETHER ACCESS APPLICATION WAS OPENED BY USER   ***
        'UserControl tells access how the database was opened
       
            appAcc.UserControl = True
               
        '***   MAXIMIZE OTHER DATABASE  ***
            appAcc.RunCommand acCmdAppMaximize
     
        Else
            MsgBox "I think you already have this open."
           
        End If
       
    End Sub

  10. #10
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    The DIR() has to encompass the whole path?

    Plus I would expect you would test for "" not " "

    I have no idea as to why you are bracketing everything in pieces.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  11. #11
    TimVB123 is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2022
    Posts
    4
    Thanks for feedback. Lack of talent and being self taught would be the main reasons for the 'peculiarities'!

    I've amended the code following your guidance and seems to work well.

    Thanks again.

    Code:
    Private Sub btnInsurance_Click()
    
    
    
    'OPEN THE USER'S OWN UNIQUE FRONT END FOR THE INSURANCE DATABASE
         
         
         
    '***   MINIMIZE CURRENT DATABASE (MSFront Database) to ensure the opening database (InsVB Front) is shown to the user   ***
        DoCmd.RunCommand acCmdAppMinimize
    
    
    
    
    
    
    '***   SET VARIABLE FOR ACCESS APPLICATION  ***
    
    
    'Set variable name and type for Access application
        Dim appAcc As Access.Application
        
    'Set variable value (open a new version of Access)
        Set appAcc = New Access.Application
    
    
    
    
    
    
    '***   SET VARIABLES FOR PATHNAMES   ***
    
    
    'Set variable name and type
        Dim strEmpName, strlaccdb, straccdb As String
    
    
    'Set variable values
    
    
    'Unique user name - Taken from user login name in Passwords form
        strEmpName = Forms![frmpassword].Form![EmployeeName]
        
    'Pathname of locked database location, i.e. the database is already open
        strlaccdb = Dir("Z:\InsVB\InsVB User Front Ends\" & strEmpName & "\InsVB Front.laccdb")
        
    'Pathname of database location
        straccdb = ("Z:\InsVB\InsVB User Front Ends\" & strEmpName & "\InsVB Front.accdb")
    
    
    
    
    
    
    '***   OPEN DATABASE IF NOT ALREADY OPEN   ***
        
    'If the pathname (file location) for the locked file has NOTHING in it then proceed and open the database
        If strlaccdb = "" Then
            
        'Make database visible
            appAcc.Visible = True
            
        'Locate and open other database
            appAcc.OpenCurrentDatabase straccdb
        
        'UserControl tells access how the database was opened
            appAcc.UserControl = True
                
        'Maximize other database
            appAcc.RunCommand acCmdAppMaximize
    
    
    'BUT if the pathname for the locked file has SOMETHING in it then do not open the database for a second time and tell user what is happening
        Else
            MsgBox "I think you already have the INSURANCE database open." & vbCrLf & vbCrLf & _
            "Please check your task bar.", vbExclamation, "OPEN INSURANCE DATABASE"
        End If
        
        
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Can't get Form to Open In Front of Access
    By MarksinMO in forum Forms
    Replies: 1
    Last Post: 09-29-2015, 01:38 PM
  2. Open backend on front end start-up
    By Ruegen in forum Access
    Replies: 10
    Last Post: 07-08-2014, 07:06 PM
  3. Replies: 2
    Last Post: 06-25-2014, 10:29 PM
  4. After front end shut down - server shows still open!
    By skyview chick in forum Access
    Replies: 10
    Last Post: 07-10-2012, 04:14 PM
  5. Unable to open front end of split database
    By Dunro in forum Import/Export Data
    Replies: 10
    Last Post: 09-17-2011, 07:37 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums