How to Kick Users out of Your MS Access Database

แชร์
ฝัง
  • เผยแพร่เมื่อ 24 ธ.ค. 2024

ความคิดเห็น • 59

  • @YvesAustin
    @YvesAustin 2 ปีที่แล้ว +1

    Thanks!

  • @seanmackenziedataengineering
    @seanmackenziedataengineering  2 ปีที่แล้ว +3

    Some notes: Server databases usually have a feature for this, but MS Access does not have it unless you program it yourself. Some situations *will* prevent the user's copy from shutting down. This includes a user leaving some message box pop up open, but not clicking any button on it. However, most of the time, this will shut them down just fine. You can tailor and clean up your notification form to your liking. Depending on your design, you may want to issue a few save and/or close commands before the system closes with DoCmd.Quit. I have used this (or very similar) deployments in production settings and it works awesome. Especially for taking care of that one user that always stays logged in!!

  • @danielazzam
    @danielazzam ปีที่แล้ว +3

    You made my day!
    This has been a pain in my ass
    Each time I wanted to modify tables in my BE , I had to call all users one by one and ask them to quit their copies of FE since they would be locking my BE
    Thank you for this video , Much appreciated .

    • @seanmackenziedataengineering
      @seanmackenziedataengineering  ปีที่แล้ว +1

      Awesome, that's great! Glad it worked for you. I remember the first time I tried this and was able to shut down about ten other users' front-end copies after hours so I could make changes to the system. Better than having to call them!

  • @10secondslearning4
    @10secondslearning4 ปีที่แล้ว +1

    Tried this, worked great but now front ends close regardless of backend box been ticked or not. Any solution?

    • @seanmackenziedataengineering
      @seanmackenziedataengineering  ปีที่แล้ว

      That seems strange - can you post the code you used? We can take a look.

    • @10secondslearning4
      @10secondslearning4 ปีที่แล้ว

      @@seanmackenziedataengineering I created every form and used the exact same code you used in the video so that there wasn't anything different except I put 60 secs on the timer to open the message window with 30secs to closing.

    • @seanmackenziedataengineering
      @seanmackenziedataengineering  ปีที่แล้ว

      @@10secondslearning4 Make sure that your system table is in the backend database and that your front ends all link to it. It sounds like they are not reading the backend table, so they do not see the changed value there.

  • @DataisKing
    @DataisKing 2 ปีที่แล้ว +1

    Wondering if you have ever transferred Access Tables into Dataverse using the wizard? When I tried, it did not transfer over any number columns.

    • @seanmackenziedataengineering
      @seanmackenziedataengineering  2 ปีที่แล้ว

      Interesting.. I have not heard of that issue. Did it replace any with GUID? I'll be taking a much closer look at Dataverse in upcoming episodes.

    • @DataisKing
      @DataisKing 2 ปีที่แล้ว

      @@seanmackenziedataengineering I'm not sure. I can only use Dataverse thru Teams because I don't have a license. I created this video that shows the process that I went thru to connect Access to Dataverse in Teams. th-cam.com/video/NTM1u54Fm_o/w-d-xo.html

    • @seanmackenziedataengineering
      @seanmackenziedataengineering  2 ปีที่แล้ว

      @@DataisKing I'll check it out!

  • @stanTrX
    @stanTrX 11 หลายเดือนก่อน +1

    Hi Sean, thanks for another helpful video. How can we prevent our fe users to play around wih our backend tables. In your example, kick out system table for instance?

    • @seanmackenziedataengineering
      @seanmackenziedataengineering  11 หลายเดือนก่อน

      For starters, you can set your startup options th-cam.com/video/M6VxBY9mt_s/w-d-xo.html and disable the bypass key th-cam.com/video/DHqOZsH5u5o/w-d-xo.html Those are good steps. There is more you can do also like add a password hash th-cam.com/video/2VrFXQd7xDQ/w-d-xo.html but the first two links can get you started.

  • @deniscloutier383
    @deniscloutier383 2 ปีที่แล้ว +2

    Merci!

  • @brentditto143
    @brentditto143 9 หลายเดือนก่อน +1

    is there any way for force close a front end DB that is an accde file? I made the file accde to limit what end users could do, but then it stops all vba.

    • @seanmackenziedataengineering
      @seanmackenziedataengineering  9 หลายเดือนก่อน

      The code in this video should work for your purpose. Make sure that you ran a debug > compile in VBA with Option Explicit on so that you catch any little typos or problems in the code. If it won't compile, that is probably why. Then recreate your accde.

  • @rehtsej066
    @rehtsej066 10 วันที่ผ่านมา

    Hi what if the backend is network drive. Im currently experiencing that if someone open the frondend in their system im not able to update the backend its shows that read only

  • @charlieweller3403
    @charlieweller3403 6 หลายเดือนก่อน +1

    I have done this with my database. I have it saved on a shared server where around 15-30 people all use the front end at once. I try to kick them out using this but somtimes it doesn't work or it might just kick 1 or 2 peope out. Do you know why this might be?

    • @seanmackenziedataengineering
      @seanmackenziedataengineering  6 หลายเดือนก่อน

      First, make sure all the users are using a db copy that has your new logic. If your app is a style that has a lot of modal pop-ups then they will interfere with the shut-down process, ie: Are you sure you want to close that form? That will stop the shut-down process. Design-wise, you want to convert that kind of behavior to use custom pop-ups that will still close on a shut-down command. Also, you may have an app where users can leave a form open (say a big data entry form) in an un-validated state. You will want to start modifying those forms so that they can exit gracefully unattended. Those kind of situations can cause issues but you can design for it.

  • @kyopan23
    @kyopan23 6 หลายเดือนก่อน +1

    How would you execute the code if you want to kick users out that have been idle for an estimate amount if time?

    • @seanmackenziedataengineering
      @seanmackenziedataengineering  6 หลายเดือนก่อน

      There's a challenge! I think you would probably need some kind of event in your app that would reset an inactive flag (maybe every time they hit OK or something like that). If they are inactive for x minutes/hours then gracefully shut down.

  • @jonjcapps
    @jonjcapps ปีที่แล้ว +1

    I am working on moving my tables to Dataverse (a few tables at a time, as I am not sure the consequences are) How can you tell if a user has a FE open and you want to boot them out and update their FE client of Access and not have a record be damaged in DataVerse?

    • @seanmackenziedataengineering
      @seanmackenziedataengineering  ปีที่แล้ว

      You can use the method in this video because it will boot all users, and it does so by just reading the remote table (in your case in Dataverse). You can't really tell if a certain user has the file open in this example, BUT it is easy to extend this logic to also monitor users and kick them out individually. Just extend the table in this video to include a field and row for each user. When the user copy checks to see if it should stay open, just change the query to only check the row with their name in it.

  • @tomfry3147
    @tomfry3147 2 ปีที่แล้ว +1

    Very useful tip. I've oftern been frustrated by users not logging out of my Access database. Not any more ....thanks to you.

  • @botskie2354
    @botskie2354 2 ปีที่แล้ว +1

    hello Sean, can you give us an example like when open a form with with date value but the value of that date is auto default date which is every end of the month , like January 31, 2022 , February 28, 2022 and son and so fort.... hope you read this and I'm glad if you help through this... thank you and god bless

    • @seanmackenziedataengineering
      @seanmackenziedataengineering  2 ปีที่แล้ว

      Do you mean that user(s) open the form to work in, and the date field is always the last day of the current month?

    • @botskie2354
      @botskie2354 2 ปีที่แล้ว

      @@seanmackenziedataengineering yes your right that what i want make in the form... but I dont know how make it...

    • @botskie2354
      @botskie2354 2 ปีที่แล้ว

      it also a big help for me if you do it thru vba code

  • @giuseppepelella2650
    @giuseppepelella2650 ปีที่แล้ว +1

    I will give a go
    Is it possible to know which user has left the backend open? This is To kick them personally afterwards

    • @seanmackenziedataengineering
      @seanmackenziedataengineering  ปีที่แล้ว

      Ha! Many times I was in the same situation. Yo can read the lock file with a text editor and you can see the computer name and the security name in the Access context, which is usually Admin unless you enabled some Access security. So you might see WORKSTATION-001 Admin or something like that. If you know who is at that workstation then you are good to go..

  • @paulskelton8799
    @paulskelton8799 2 หลายเดือนก่อน +1

    This is great, thanks for sharing your knowledge

  • @SamuelTondé
    @SamuelTondé 4 หลายเดือนก่อน +1

    Salut. Très interessant mais je souhaite une version en francais ou soustitré en francais.

    • @seanmackenziedataengineering
      @seanmackenziedataengineering  4 หลายเดือนก่อน

      Mon français n'est pas assez bon pour cette tâche. Je peux peut-être faire des recherches sur les sous-titres. :-)

  • @mauriciobick9855
    @mauriciobick9855 2 ปีที่แล้ว +1

    Pretty cool Sean.
    Thank you for sharing.
    What would be cool is to have a countdown text box or label.

    • @seanmackenziedataengineering
      @seanmackenziedataengineering  2 ปีที่แล้ว

      That would be a good feature!

    • @mauriciobick9855
      @mauriciobick9855 2 ปีที่แล้ว +1

      @@seanmackenziedataengineering could you show us how it’s done?

    • @seanmackenziedataengineering
      @seanmackenziedataengineering  2 ปีที่แล้ว +2

      You can just add a few lines of code to zfrmSystemExit so it looks like this:
      Option Compare Database
      Dim intCountDown As Integer
      Private Sub Form_Open(Cancel As Integer)
      intCountDown = 11
      End Sub
      Private Sub Form_Timer()
      intCountDown = intCountDown - 1
      Me!Label0.Caption = "The system will exit in " & intCountDown & " seconds.."
      DoEvents
      If intCountDown = 0 Then DoCmd.Quit
      End Sub
      Important: Then change the form timer interval value from 10000 to 1000
      You will see the message in the pop-up decrease 1 second from 10 to 1 until closing.

    • @miles6875
      @miles6875 ปีที่แล้ว

      @seanmackenziedataengineering Will never take for granted the high quality responses you give to your viewers. Thank you Sean

  • @simonmaersk
    @simonmaersk 2 ปีที่แล้ว +2

    Great video with useful tips, thank you!
    I chuckled a little every time you said "kick users back end" 🤣

  • @minhajabbasy9582
    @minhajabbasy9582 ปีที่แล้ว +1

    Very nice video. Keep up the good work. Stay blessed.

  • @mikewashington4188
    @mikewashington4188 2 ปีที่แล้ว +1

    Great technique

  • @j.dasilva4567
    @j.dasilva4567 หลายเดือนก่อน +1

    Top.

  • @ssdusd
    @ssdusd 2 ปีที่แล้ว +1

    Awesome 👌

  • @interestingamerican3100
    @interestingamerican3100 2 ปีที่แล้ว +1

    Commenting for the algorithm.

  • @jerzykepinski
    @jerzykepinski 2 ปีที่แล้ว +1

    this is goood

  • @gerfer6261
    @gerfer6261 2 ปีที่แล้ว +1

    Good back side kicker😂🤾
    I have a similar one monitors text file name ( of course from mr. google)
    But this is much simpler
    One issue I encounter is if user left the terminal in edit mode without exiting the field then the issue exists
    I have to give this one a go
    Sounds great 🎉🎉🎉
    Merry Christmas ( don’t disturb till next year😅 please)
    Good on you mate sharing your knowledge throughout