How to Kick Users out of Your MS Access Database
HTML-код
- Опубликовано: 30 июл 2024
- In this episode, we'll take a look at how to add a simple feature to your application that will allow you to boot users out of your Access system on demand, so that you can do administration or design changes on your system. If you have a network system, this can save you A LOT of time when users leave their application open and their desk is in some distant office where you either have to physically go there, or call them, or manually go into their session and force-close their windows session, or whatever. This method allows for a graceful shut-down of all user copies for the majority of Access based systems. This will shut down all systems running your front-end in the vast majority of cases (though there are exceptions, see the comments). Let's go kick our users out of our database!
Related Videos:
How to disable and enable the Bypass Key on your MS Access app so users cannot bypass Autoexec/Forms
• How to disable and ena...
Link and Relink Your Access Front-End App Using vbScript and a Table-Based Approach
• Link and Relink Your A...
How to Use vbScript to Relink Your User's MS Access Application Front-End Automatically
• How to Use vbScript to...
Use vbScript to Deploy your Access Front End, Create Shortcuts, and Check for Updates on User Login
• Use vbScript to Deploy...
How to Encrypt and Hash Passwords in MS Access
• How to Encrypt and Has...
How to Encrypt Sensitive Data in Microsoft Access
• How to Encrypt Sensiti...
How to Kick Users out of Your MS Access Database
You are watching this video now!
How to Get the IP Address in Microsoft Access
• How to Get the IP Addr...
How to Get the Windows Username in MS Access
• How to Get the Windows...
How to Set Basic Startup Options on Your Microsoft Access Database
• How to Set Basic Start...
Join me on Patreon!
/ mackenziedataengineering
Demo of my BZ RDP Cloaker:
www.patreon.com/posts/how-to-...
Check out some tech (and support my channel!):
www.amazon.ca/shop/seanmacken...
Want the code from this video?
mackenziemackenzie.com/downloads
For developers looking for a new role, check out and sign up:
www.toptal.com/qKaO2b/worlds-...
Needing to hire technical resources for your project? Get the best:
www.toptal.com/qKaO2b/worlds-...
Contact form you can find at www.mackenziemackenzie.com/
Follow me on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksynergy.com/fs-bin/...
Got a RUclips Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy.com/seanmackenz...
#msaccess #vba
Thanks!
Much appreciated! Glad it worked for you.
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!!
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 .
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!
Great video with useful tips, thank you!
I chuckled a little every time you said "kick users back end" 🤣
Sometimes you just have to kick ‘em! Haha 😂
Very useful tip. I've oftern been frustrated by users not logging out of my Access database. Not any more ....thanks to you.
Glad it helped! cheers
Great technique
Thank you! Cheers!
Very nice video. Keep up the good work. Stay blessed.
Thank you! You too!
Awesome 👌
Glad you enjoyed!
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?
For starters, you can set your startup options ruclips.net/video/M6VxBY9mt_s/видео.html and disable the bypass key ruclips.net/video/DHqOZsH5u5o/видео.html Those are good steps. There is more you can do also like add a password hash ruclips.net/video/2VrFXQd7xDQ/видео.html but the first two links can get you started.
Pretty cool Sean.
Thank you for sharing.
What would be cool is to have a countdown text box or label.
That would be a good feature!
@@seanmackenziedataengineering could you show us how it’s done?
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.
@seanmackenziedataengineering Will never take for granted the high quality responses you give to your viewers. Thank you Sean
Wondering if you have ever transferred Access Tables into Dataverse using the wizard? When I tried, it did not transfer over any number columns.
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.
@@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. ruclips.net/video/NTM1u54Fm_o/видео.html
@@DataisKing I'll check it out!
Merci!
Thanks, very much appreciated!
@@seanmackenziedataengineering You just made my day !!! Thanks !😀😀😀
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.
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.
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?
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.
How would you execute the code if you want to kick users out that have been idle for an estimate amount if time?
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.
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?
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.
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
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?
@@seanmackenziedataengineering yes your right that what i want make in the form... but I dont know how make it...
it also a big help for me if you do it thru vba code
Tried this, worked great but now front ends close regardless of backend box been ticked or not. Any solution?
That seems strange - can you post the code you used? We can take a look.
@@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.
@@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.
this is goood
Thanks!
I will give a go
Is it possible to know which user has left the backend open? This is To kick them personally afterwards
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..
Commenting for the algorithm.
Thanks!
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
Thanks and Merry Christmas! cheers!