Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Auto Shutdown of Database (Frontend and/or Backend)

    Experts:

    Much has been published on the topic "Kick user(s) out of an Access database/backend". In fact, there appears to be an overwhelming amount of recommendations how to "gently" do so w/o a) causing potential data errors and b) ending up with a number of frustrated users.

    At this time, I have come across a simple, but effective, example database (published in 2005 @ https://www.databasejournal.com/feat...aintenance.htm#). I like the simplicity of the demo version and it appears it works just fine as long as I am in the same frontend.

    Here' how I tested the demo DB:


    a. I split the database into "backend" (BE) and "frontend" (FE) and linked the BE table to the frontend.
    b. For mimicking multi-user login, I created two shortcuts to the FE. I called the 1st shortcut = "User 1"; 2nd shortcut = "Admin".
    c. Then, for testing purposes, I had a user login as "User 1" while I selected shortcut "Admin".
    d. As the Admin, I then executed the Logout form.
    e. For me (admin), the 3-minute countdown started almost immediately. For User #1, however, there was a slight delay but ultimately the "warning message" appeared and eventually both databases were shut down after 3 minutes.

    THIS WORKED GREAT!!

    BREAK BREAK...

    Now, here is where (I believe) I need some assistance given that my process of using the FE is different. Please allow me to recap the differences:
    1. As mentioned in b) and c), I created two shortcuts (User #1 & Admin) -- both of them were linked to the same FE on the network though.
    2. In my case, however, I do NOT share the same FE on the network. Instead, our users have their individual copies of the FE on their client machines. Now, in the event I make a change to the MASTER FE on the network, the client FE will automatically download a copy of the MASTER FE which will then replace the FE on the client machines. The AutoUpdater process works great (see details @ http://www.utteraccess.com/forum/Eas...r-t304808.html) and I do NOT want to change how users automatically get the latest version of the MASTER FE. But they, the "AutoUpdater" topic is another topic... I digress.

    Back to the AutoShutdown (demo version). I believe this AutoShutdown process worked given that both User #1 & Admin ultimately used the same FE (on network), right? Alternatively, in my process, I'm not 100% certain if the "shutdown warning message" would appear on User #1's FE given we have individual FE copies on our C-drive. So, I don't see how my initiating the "shutdown" process would generate the warning pop-up on another person's client FE.

    My question: Am I missing something obvious? If my assumption, however, is correct and the recommended auto shutdown won't work in my situation, is there another way I could tweak the example file? If so, how?
    Lastly, if the latter is not feasible, does anyone have a recommendation for another solution that supports the shutdown of individual FEs on various client machines?

    Thank you,
    EEH
    Attached Files Attached Files

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    My approach together with an example app is described in this post at another forum: https://www.access-programmers.co.uk.../#post-1546375. You may need to read the whole thread to understand the context

    It works by setting a boolean field Kickout = True in the shared BE file when you need to close a database for routine maintenance.
    The FE contains a hidden form that checks the kickout value every 30 seconds. If it is true, a shut down procedure is initiated, and users have 5 minutes to complete their current task before the app is shut automatically. New users are unable to start the app when Kickout = True
    The result is all users can be guaranteed to have left the app within around 6 minutes after which maintenance can begin.

    However the above isn't needed for users to receive the latest version of the FE from the network.
    For my approach to that, see my recent answer at https://www.access-programmers.co.uk.../#post-1674918
    Last edited by isladogs; 03-06-2020 at 01:29 AM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Isladogs -- your version is very similar to the demo version I attached in my original post, except your example is much cleaner.

    In the 3rd para, you referenced "... above isn't need for users to receive the latest version of the FE from the network..." -- just to clarify, are you indicating the the logout won't work based on my checking / downloading the latest FE from the network and replacing the FE on the client. That's the process I'm currently using via the AutoUpdater.

    Thank you!

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi
    The kick out process works effectively.

    I'm saying that my autoupdater works in a slightly different way as explained in the second link provided. In my approach the desktop shortcut is actually a starter app which looks like a splash screen. When clicked it checks for a newer version on the network and:
    1. If its the latest version, the starter app loads the main app and shuts itself automatically
    2. If a newer version exists, the file(s) are automatically copied from the network overwriting existing files. This is done using a Windows API so its very fast. When this is complete after a few seconds, it follows the same process as step 1.
    The starter app can even update itself if a newer version is available

    As the version check is performed before the main app is loaded, there is no need to lock the FE.
    Its all fast and seamless as far as end users are concerned

    Also in answer to the question you originally asked (and shown in my notification email), there is no need for admins to open the BE with or without a shift bypass.
    When admins do need to lock the system, they are given the option of continuing to work in the program. If so, the kickout can just be reversed later when maintenance is completed. I also have two small utility apps AutoShutdown and UndoShutdown. Each contains one linked table tblKickout from the BE. When either is run an autoexec macro runs an update query to set the value of the Kickout field. Simple!
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Colin:

    I've spent some time this AM and further reviewed your Kickout demo. From my perspective, it works really great.

    And, yes, you're right... no need to disable the Bypass key for the BE. I realized it after I posted the last comment, so I edited it. Naturally, you received email w/ my original comment/question.

    Break

    I integrated your forms into my actual version (the one that using AutoUpdater) to check whether or not a newer FE exists on server. Anyhow, this I what I noticed when using it in the actual database:
    1. Actual shutdown-worked great. However, many users (including myself) use extended monitors (2nd monitor). Thus, when I moved the Access window to my secondary screen, the "Warning" form (i.e., frmLogoutStatus) did NOT pop up on the same monitor. In fact, once I clicked "Ok", the warning message popped up "behind", e.g., "Outlook". So, ultimately, depending on a user's preference (move software windows around; having multiple windows open on both monitors, etc.), the warning message may not be seen by a user. My question: Is there a way that the frmLogoutStatus will pop open again **in front** of other windows.

    2. Similarly to #1, I used, e.g., a "Staff Member Lookup" form to navigate to a particular user. Once I locate the user and click "Open" the actual staff member form comes up. Now, in this case I kept my Access window on the primary screen and frmLogoutStatus popped up in in front of the staff member form. HOWEVER, I could NOT click on neither "Ok" nor "Quit". I first had to close down the staff member form and then the staff member lookup form. At that point, clicking on "Ok" closed, e.g., frmLogoutStatus. My question: Why weren't the two command buttons "clickable"?

    3. Another unique difference between the example demo and my actual DB was the "Lockout" form. It worked fine as you recommended in the demo version. Beautiful! However, in my DB users are prompted to login to the FE with their username/password credentials. Once they did, the Lockout form did NOT pop open even though the DB was in "maintenance status". Not sure if my "AutoUpdater" (it merely calls the FE on the client) is interfering. Any thoughts on that scenario.

    I'd truly appreciate if you have recommendations for #1 and #2; I could work around the Lockform not preventing users to lock it. Naturally, if you have recommendations about #3, I welcome your ideas, too.

    I apologize for this lengthy follow-up. Thousand thanks for your help in advance (and thus far).

    Cheers,
    Tom

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Some your questions are quite difficult to answer without replicating the situation. As I'm answering in my tablet, I only have one screen currently available.
    Anyway, here's a first attempt to answer your questions. I may do a follow up when I have a dual monitor setup available.

    1. I've never found this to be an issue with my clients...and I've used this system for almost 15 years
    Changing the zOrder is difficult especially when you are talking about different programs being used. You could consider workarounds such as:
    a) use code to prevent the Access app being dragged onto a secondary monitor
    b) use another method of grabbing the user's attention - see my Attention Seeking app http://www.mendipdatasystems.co.uk/a...eek/4594398116

    2. I'm guessing your form is modal so its preventing Users clicking anywhere else...even if that window is 'on top'.
    As an experiment, try changing your form to modal=no. Also try setting frmLogoutStstus to modal=yes.

    3. I'm surprised by this. All my apps that use this system have a user login form (user name/password).
    When Kickout is set true then frmLocked appears for any new user trying to logon (not frmLogoutStatus). After 10s, the app closes.
    If you aren't seeing that, its probably the same issue as above as frmLocked is both modal and popup.
    Last edited by isladogs; 03-07-2020 at 01:18 AM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Colin -- thank you for the prompt response. I will look into it and keep you posted. More to follow.

    Cheers,
    Tom

  8. #8
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Colin:

    I've reviewed/changed the pop-up & modal form properties. Allow me to recap where I currently stand on the "Auto-Shutdown".

    First though, attached are two (2) databases:
    1. Your sample demo (with slightly design changes in the form IOT our organization's DB).
    2. A totally trimmed down version of my org's DB (with no real functionality; however, the login process still works the same).

    Process of the "Sample Demo":
    1. Within "Frontend.accdb", re-link the table "T903_Admin_DB_Kickout" from "Backend.accdb".
    2. Follow steps as you defined in your posting.
    3. Once in maintenance status, form "F105_N1S_Locked" will kick in upon attempting to re-open the database.

    Process of the "My Database":
    1. Ensure to "Enable Content" when login forms pops up.
    2. Login using the following credentials: Username = "Doe, John"; Password = "admin".
    3. In top right listbox (Admin), click "System Maintenance (Kick Out DB Users).
    4. Follow steps as you defined in your posting.

    Now, the following happens:
    a. If I click "Ok" and then move Access window from primary monitor onto secondary monitor, form "F105_N1S_LogoutStatus" will re-appear on the primary (vs. secondary) monitor after 1 minute. That's ok!
    b. However, if another software window (e.g., MS-Outlook) is open on the primary monitor, form "F105_N1S_LogoutStatus" will re-appear **behind** Outlook; thus, the user will NOT see the warning message.
    c. Regardless of whether or not user saw the warning message, the database will shut down after 5 min. Good (well, not so good if user never saw the warning message)!

    Next, login back into while in "Maintenance Status":
    a. Login back as "John Doe".
    b. I get to see my main menu. That is, form "F105_N1S_Locked" never kicked in. Please do keep in mind that it works great in the database "Sample Demo".

    On another note, I have an "Autoexec" macro & "M06_DB_Startup" module as part of my startup routine. I recently integrated these two (they work great) in order to add data to table "T902_Admin_SystemUserTracking".
    Ultimately, I am NOT sure if the macro & module may interfere with the "Locked" form.

    Is there any chance you could have a look at my trimmed down "My Database" as to why a) form "F105_N1S_LogoutStatus" does NOT pop up in front of other software in the event I moved Access to another monitor and b) form "F105_N1S_Locked" does NOT kick in when user attempts to login while DB is in maintenance status?

    Thousand thanks in advance!
    Tom
    Attached Files Attached Files

  9. #9
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Colin... here's a quick update. I resolved the issue with the "Locked" screen NOT popping up.

    After posting my posting my last question, I noticed the following:
    - Your DB startup/display form was/is set to form "F105_N1S_LogoutTimer". In my actual DB, it is set to "F001_SYS_UserValidation".
    - I then moved the VBA code (below) from "F105_N1S_LogoutTimer" into "F001_SYS_UserValidation".
    Code:
    Private Sub Form_Load()
    
        'Hide Navigation Pane/Shutter Bar upon opening the database (can be undone via Admin tab)
        DoCmd.NavigateTo "acNavigationCategoryObjectType"
        DoCmd.RunCommand acCmdWindowHide
        
        'If database is in "maintenance status", users will NOT be able to log into the system
        fLogout = DLookup("[Kickout]", "[T903_Admin_DB_Kickout]")
       
        If fLogout = True Then
           DoCmd.OpenForm "F105_N1S_Locked"
           DoCmd.Close acForm, Me.Name
        End If
    
    End Sub
    Again, I deleted the same code from "F105_N1S_LogoutTimer". Now, when in maintenance mode, the lockout form pops up.... GREAT!!!!

    Now, I'm only left to figure out how to ensure that the status/warning form always "sits" on top of any other windows. I you have additional thoughts on the form properties, I'd welcome your feedback.

    Cheers,
    Tom

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi Tom
    I'd just finished fixing your database when I saw you had also solved it.
    I've done it a slightly different way so I'm attaching my version for you to look at

    Your autoexec form includes code that only runs if the app is trusted. That is pointless as no code will ever run when an app isn't trusted.
    I've scrapped your autoexec and replaced it with one to load form frmLogoutTimer hidden which is how its meant to work.

    As in your modified code that loads frmLocked if kickout is true but if false it now runs your function Startup.
    This now gives correct functionality.

    I've not yet looked into the zOrder issue. If I have time, I'll investigate it later today or tomorrow...but I doubt its easy to do.

    A few other things.
    1. All code modules should have the lines Option Compare Database followed by Option Explicit. I've added one or both where missing
    2. I recommend you change the code used to get user name and computer name. The very old API code you use needs modifying depending on bitness.
    Much simpler code is available using Environ or WScript and as neither uses APIs, they work in both 32-bit and 64-bit.
    See these pages on my website http://www.mendipdatasystems.co.uk/g...ame/4594424315 and http://www.mendipdatasystems.co.uk/g...ame/4594488772
    I recommend using WScript.
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Colin -- thank you so very much for reviewing/fixing the VBA code issues. I very, very much appreciate your help. I'll integrate all of your recommendations into my actual DB.

    WRT to the popup NOT necessarily popping up in front of the Access window (if Access window was moved), I came across a potential solution (http://www.utteraccess.com/forum/pop...-t1994902.html). The demo file (attached) looked promising as the pop-up window popped on top of Access even when the Access window was moved.

    Once I integrated the proposed solution in the status form, the results didn't change.

    Thank you... I appreciate your continued support.

    EEH
    Attached Files Attached Files

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I've seen that thread before but think its purpose is just to save a form position no matter which monitor its on.

    As a test, I've tried adding the following code to the Form_Load event of frmLogoutStatus

    Code:
    Private Sub Form_Load()
    On Error GoTo Err_Handler
       
       DoCmd.RunCommand acCmdBringToFront
       
    Exit_Handler:
        Exit Sub
    Err_Handler:
        If err <> 2046 Then
            MsgBox "Error " & err.Number & " in Form_Load procedure: " & err.Description, vbExclamation, "Error"
        End If
        Resume Exit_Handler
    End Sub
    
    As I suspected, it only affects a form's zOrder within Access. In other words it has no effect on the overall zOrder in Windows taking into account other applications such as Outlook.

    I've also tried a few other approaches without success.
    I'll look into using APIs to hopefully find a solution

  13. #13
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Colin:

    Thank you for the additional information.

    As I have integrated your change recommendation, I now have come across another stumbling block. I am hopeful there's a solution to this problem.

    Background:
    - As you have highlighted in your Shutdown procedure, one of the key elements is to provide the user a 5-min warning to finish their work.
    - I am confident any developer/user would agree that such is a great idea.

    Process within my DB:
    - When I login to the DB, the "Main Menu" drives pretty much all of my operations.
    - What I presented in the earlier "Admin" listbox is only the "tip of the iceberg" WRT to accessing queries, functions, reports, etc.
    - Now, in the revised version (attached) I added one link (Forms listbox) called "View Staff Members".
    - Clicking this link calls form "F121_N1S_StaffmemberList". From here, I can search through the, e.g., hundreds/thousands of record. (I disabled the search feature in this example though).
    - So, simply click on "Open" next to any staff member's last name. This in turn then brings up the actual staff members form.
    - Finally, I use the same process (search form to actual record form) for jobs, branches, etc. etc.

    Issue:
    - When a user has the actual staff member form (e.g., Clint Eastwood) open, the form "F105_N1S_LogoutStatus" status does NOT show up during the 5-min countdown.
    - Now, for testing purposes, I first linked my Main Menu shortcut directly to form "F122_N1S_Staffmember". In that case, the warning message DOES pop up in front of, e.g., "Clint Eastwood".
    - But again, this is not how I access many of my forms. So, I presume there's something in the form "F121_N1S_StaffmemberList" that prevents the message from being visible, e.g., the 4-min count down.

    Unfortunately, users have come to appreciate the "F121_N1S_StaffmemberList" in order to quickly find an employee. That said, I am reluctant to switch the overall process where they go directly to the record and then navigate from one record to the next.

    All that said, do you have a recommendation/solution that will allow using the "F121_N1S_StaffmemberList" (or any other alike form not included in this demo) and, thus, the warning message will be visible once the users perform their data entry on an employee record?

    Cheers,
    Tom
    Attached Files Attached Files

  14. #14
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    ... clarification to the process.

    1. If I were to disable F121_N1S_StaffmemberList and directly open F122_N1S_Staffmember via the menu, the warning DOES pop up on top.
    2. If I open F121_N1S_StaffmemberList and do NOT select an employee record, the warning DOES pop up on top.
    3. However, based on my actual process, bringing up form F121_N1S_StaffmemberList and then F122_N1S_Staffmember, the warning does NOT show up.

    #3 is the method for drilling down to a record, so I'm hopeful there's a solution to this issue.

    Looking forward to your thoughts/recommendations.

  15. #15
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Unless I'm losing the plot, the file you uploaded doesn't contain the items you mentioned. In fact it may be the FE I uploaded earlier...
    Please check and get back to me.

    However I can say that I've used this system for over 15 years with multiple users and never had the issue you're describing.
    So this new issue should be fixable.
    Are you other forms popup and?or modal or opened using the acDialog option?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Auto Backup Backend When Opening Frontend
    By Elfear in forum Programming
    Replies: 3
    Last Post: 04-25-2019, 10:58 AM
  2. Replies: 3
    Last Post: 07-03-2017, 10:00 AM
  3. Replies: 4
    Last Post: 05-25-2017, 06:07 AM
  4. Replies: 11
    Last Post: 02-24-2017, 06:25 PM
  5. Frontend Backend Database Questions
    By data808 in forum Access
    Replies: 29
    Last Post: 03-26-2014, 11:58 PM

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