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!
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 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.
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 ua-cam.com/video/M6VxBY9mt_s/v-deo.html and disable the bypass key ua-cam.com/video/DHqOZsH5u5o/v-deo.html Those are good steps. There is more you can do also like add a password hash ua-cam.com/video/2VrFXQd7xDQ/v-deo.html but the first two links can get you started.
@@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. ua-cam.com/video/NTM1u54Fm_o/v-deo.html
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.
@@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.
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.
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.
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
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..
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!
Much appreciated! Glad it worked for you.
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!
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
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!!
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 😂
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
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 ua-cam.com/video/M6VxBY9mt_s/v-deo.html and disable the bypass key ua-cam.com/video/DHqOZsH5u5o/v-deo.html Those are good steps. There is more you can do also like add a password hash ua-cam.com/video/2VrFXQd7xDQ/v-deo.html but the first two links can get you started.
Very nice video. Keep up the good work. Stay blessed.
Thank you! You too!
Great technique
Thank you! Cheers!
Merci!
Thanks, very much appreciated!
@@seanmackenziedataengineering You just made my day !!! Thanks !😀😀😀
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. ua-cam.com/video/NTM1u54Fm_o/v-deo.html
@@DataisKing I'll check it out!
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.
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.
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.
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.
Awesome 👌
Glad you enjoyed!
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
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..
this is goood
Thanks!
Salut. Très interessant mais je souhaite une version en francais ou soustitré en francais.
Mon français n'est pas assez bon pour cette tâche. Je peux peut-être faire des recherches sur les sous-titres. :-)
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!