Question Kicking users out of Access

[Linda2431]

I am a novice user of Access so please be patient with me. J I have a need to occasionally to “kick” users out of Access – for example if our database locks and I need to have everyone get out of the database so I can compact/repair it. At times a user has gone to lunch and is in the database. We use a front end and back end database. The users execute a batch file to use the front end database which puts a copy of the front end on their desktop. I found a posting online which is supposed to “kick” users out, but it isn’t working for me. Actually, this works and kicks ME out of the front end database, but doesn’t kick anyone else out.
This is what I did in our front end database:
1. Created a table and named it "tblLogout" which has one field -- a "Yes/No" field called "LogoutYesNo"
2. Created a form called frm_Logout
3. Created an unbound text box in the form and named it “txtMinsSecs”
4. Added the code in the form and set the form’s timer interval to 150.
5. To kick people out, I checked the "LogoutYesNo" field in "tblLogout"
6. I then run the form, which pops up the form with a countdown stating that the database will be shut down in 1 minute.
This works on MY computer only – it kicks me out of the front end. How can I get this to work on everyone’s front end copy? (I have attached a blank database with only the table and form included.)
Thank you.

 

[jdraw]

See SydneyGeek's link here
http://www.mrexcel.com/forum/microsoft-access/664456-force-user-off-database.html

More info:

Sydney geek's approach is described here
http://www.datawright.com.au/access_resources/access_tips.htm#tip_boot_users

Another method with a sample database is here
http://www.mrexcel.com/forum/microsoft-access/664456-force-user-off-database.html

and another link
http://www.candace-tripp.net/download/logoff2k.zip

Found it
http://www.access-programmers.co.uk/forums/showthread.php?t=238907

 

[Linda2431]

Thank you - I'll have to look at it at home. Our corporate server is blocking the link

 

[jdraw]

There was another post or 2 just recently on this subject. I'll see if I can find something and update this post.
Anyway I think Denis/SydneyGeek's approach was a favorite technique.

 

[Linda2431]

jdraw: I got Denis/SydneyGeek's approach to work in my test environment. (As I said, I'm a novice user of Access so I had to experiment with the code a bit this morning . . . ) Our users have a FE that they copy to their desktop. To confirm, as long as the DatabaseOn.txt file is in the same directory as the FE and BE copies, this should work to close them out, correct? Thank you for your assistance. This has been extremely helpful for me!

 

[Linda2431]

Question - I set up the frmMonitor and frmClock forms and the text file on my production database and was going to test it later this afternoon. One of the users got out of the database during lunch and logged back into the FE. They immediately got the countdown form -- I'm assuming because the DatabaseOn.txt was not found on their desktop. (The users start their FE database by a batch file which copies the FE to the desktop.)

I did take out the " DoCmd.OpenForm "frmMonitor", , , , , acHidden" code from my start-up form so this doesn't happen again.

Is there a way for me to use this process when the FE is copied to the desktop? Thank you.

 

[SydneyGeek]

There are a couple of things you could do.
One is to modify the batch file so that it also creates the DatabaseOn.txt file

Another is to add this function to a new module:

Function BackendPath() As String
    Dim strPath As String
    Dim tdf As DAO.TableDef
    Dim i As Integer
    
    For Each tdf In CurrentDb.TableDefs
        If InStr(1, tdf.Connect, "DATABASE=") > 0 Then
            strPath = tdf.Connect
            Exit For
        End If
    Next tdf
    'trim the left end of the path
    strPath = Mid(strPath, InStr(1, strPath, "DATABASE=") + 9)
    'trim the right end of the path
    For i = Len(strPath) To 1 Step -1
        If Mid(strPath, i, 1) = "\" Then
            strPath = Left(strPath, i)
            Exit For
        End If
    Next i
    BackendPath = strPath
End Function

And in frmClock, change this...

    fn = Dir(CurrentProject.Path & "\DatabaseOn.txt")

to this...

    fn = Dir(BackendPath() & "DatabaseOn.txt")

The clock form will now look in the backend database's directory for the DatabaseOn.txt file

Denis

 

[jdraw]

Thanks Denis.

 

[Linda2431]

I apologize -- I am just learning Access so I am having some difficulties . . .
I am getting errors in my test environment -- I am new to modules and codes . .

I have attached the forms and modules in the attached database. If you have time, can you see what I am doing wrong? Again, thank you very much for your assistance. I apologize for being dense . . .:banghead:

 

[SydneyGeek]

Hi Linda

The name of the text file is really important. In the tutorial it's DatabaseOn.txt -- and that is the exact name the code goes looking for. If you use any other name for the text file you need to adjust the code in frmMonitor so that the exact name appears there as well.

I'm assuming that you have Form1 as a startup form in your database? That is what loads frmMonitor in the background. To set a startup form you need to go to the Options in the Home button (the jellybean in 2007, the File tab in 2010), find the Current Database item on the left, and then select Form1 in the Display Form: dropdown list.
In Access 2003 and earlier it's in Tools > Startup.

Restart the database once you have made those changes and see how you go.

Denis

 

[Linda2431]

I did what you suggest above, and still receiving errors. I am using 2007. But now seems to not be able to find BackendPath(). I did add this as a new module -- am not sure if I added it correctly and the code is correct. I have attached the error message I'm receiving. I'm sorry -- I'm reading as much as I can about Access and am learning rapidly, but am probably still frustrating to an experienced user such as yourself . . . thanks.

 

[Linda2431]

I may have posted my response too soon -- I added the module to the frmMonitor (which I'm sure where it was supposed to be to begin with). I'm going to continue testing, but I think I may have gotten it right this time. Thank you very much! You have been extremely helpful and I appreciate it very much!

 

[Linda2431]

It's up and running -- thanks so much to both of you!!!