VBA code to kick off logged users before maintenance 39

[Lizard]

Hi everybody,

I'm regularly faced with the problem that I see users logged on my DB that prevent me from running my daily maintenance because I cannot get exclusive access to the DB.

As I don't have knowledge in VBA, would anybody know where I could find a "plug-and-play" VBA code to allow me to kick out logged users ?

Thanks.

Olivier

 

[Lizzer]

I have the exact same problem.. in fact a friend of mine accused me of signing in as Lizard because the problem was so similar. Our Help desk says they are the only ones who can view who is in the file. I'd like to be able to do this too. Help from anyone would be appreciated.

 

[Francescina]

Here is what I found in the ACCESS ONLINE ENCYCLOPEDIA (

http://www.unsoftwareag.com,

Article Code: N2)

How do I log out all users remotedly ?

From time to time it is necessary that all logged-in users leave the application in order to maintain or backup the backend.
The remote log out can be automatised in simply changing one single value in a table field of the backend.
The following steps are necessary:


Create a field [LogOutStatus] in a system table of the backend that holds the values 1, 2 or 3
Create a form in the frontend that stays open permanently (hidden)
Activate the timer interval of this form
Write code for the OnTimer event to control the field [LogoutStatus]
Write code to leave the frontend
Write code to verify the content of [LogOutStatus] on opening the frontend
The functioning:

The OnTimer-Event checks the value of [LogOutStatus]:
1 = nothing
2 = Display message "Application will shut down in a moment
3 = Another message Message if desired and closing frontend


On opening the frontend the same test has to be made.
Code examples:


Ontimer-Event select case dlookup("[LogOutStatus]","tblSystem&quot
case 1
' nothing
case 2
call MessageBeforeShutDown

case 3
' special Code for your application
application.quit
end select

Sub MessageBeforeshutdown
dim strMessage as string
strMessage="ATTENTION: The database has to be shut down in 5 minutes" _r> & "Please finish your work and leave the program."
msgbox strMessage

end sub

Please note:

- The time between changing the value of [LogOutStatus] and the shut-down of the frontends depends on the setting of the timer interval
- You need to control via VBA that the data manipulation will be terminated correctly
- All open objects have to be closed on shut-down
- You need to inform the users when the application is up again.

 

[LonnieJohnson]

Great reply. This is something that I was needing also. It is simple and does the job for me. Everything is contained within Access and I don't have to worry about network interface messaging. ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[MichaelRed]

LonnieJohnson,

If you thought Francescina 's reply was "Great", why didn't you vote for her? It is easy, just click on the "Let ... know this post was helpful" link at the bottom of the SPECIFIC post! Gives her a Purple/pink star!
MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[DbaseInc]

What I have done in my systems is create a table called tbl_Maintenance. I have four fields. StartTime, EndTime, Message, ForceOut. On log on, Switchboard, and main forms I have a on timer event that test for existance of record in the table. I have a global variable that counts number of notification. When Global Variable reaches 5 it then boots them out. This gives the user about 2 minutes to complete their work. It is not good to boot people out without allowing them to finish their work.

DbaseInc@Aol.com

 

[LonnieJohnson]

Thanks Michael, but I did vote for her.

Thanks again Francescina. ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[Francescina]

I feel very honored, but in fact what I did was just a "copy and paste" of an article in the Access online encyclopedia.
Happy programming to everybody!

 

[MichaelRed]

The 'honor' is deserved. YOU found it. YOU posted it. YOU helped Lizzard, Lonnie and me. And that is only the list who overtly acknowledged the help.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Quehay]

RE: the need to view who is in:

The following routine will give you a list that can populate a textbox (of course you'll want error trapping and set your objects = nothing in the exit).

There's also a download at Microsoft, the LDB viewer exe:

http://support.microsoft.com/support/kb/articles/q128/8/14.asp

Sub ShowCurrentUsers
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strUsers as string

Set conn = New ADODB.Connection


With conn
.connection = currentproject.connect
End With

Set rst = conn.OpenSchema(adSchemaProviderSpecific, , _
"{947bb102-5d43-11d1-bdbf-00c04fb92675}&quot

Do Until rst.EOF

struser = rst!COMPUTER_NAME & vbcrlf

struser = struser & rst!CONNECTED & vbcrlf

rst.MoveNext
Loop

msgbox strusers

End Sub

 

[Marty2of5]

Francescina

I'm trying this out and when I call MessageBeforeshutdown, I need to get an OK to continue. If the user is away from their desk and they don't OK out of the msgbox before the five minutes is up, nothing shuts down.

Any suggestions?

Marty

 

[Francescina]

I recommend you to replace the msgbox with a form that pops up (with sound and a gif) and then closes automatically after lets say 5 seconds.
This is the solution that I developped currently but you do not need necessarily to inform the users.

 

[Scoty]

Way cool.
Thanks
Scotty : bows in appreciation
You got my VOTE

 

[dleiba]

Excellent job!!

Thanks ALOT guys!

Dominic

 

[Terpsfan]

Francesina, true you copied and pasted a solution from another source. Some here would quibble with that and snicker. The hallmark of a good programmer is not knowing everything, because we don't, but knowing where to go to find the solution. I will use this as well in my database applications. That's why I love these forums, you can find undocumented solutions that you wouldn't find in any book or help file. I'm not a big fan of Access help. Normally I go straight to the Microsoft Knowledge base for anything advanced.

 

[thobholl]

I am a novice Access programmer, and I need a little more help with this one. I follow the logic, but I lack the experience. Can someone post the entire code for this project?

 

[Mikeauz]

Hey if anyones still following this post I find it a lot cleaner to use a hidden listbox on every form instead of a dedicated Kickout form.

The on load event and then timer on each form(Requerys the listbox) event then just checks the listcount of the listbox and opens the warning form or 'About to close' form accordingly.

Only change needed is that it works of how many rows are in you maintenaince table rather than the value in one field

I would also try not to use msgboxes too much in your db as access wont boot someone out until they click okay, using pop up forms instead should alleviate this.

 

[AccessUser22]

Excellent posting. I've been looking for something along these lines. I would also like a way to view myself who is currently logged in without kicking them out. I'm sure I can find that around here somewhere or modify this to work for me.

Question - When the database boots out the user, does this cause any corruption issues?

 

[Mikeauz]

AccessUser22,

I've used this method heaps and never had any corruption issues with it. I would say it's because you are simulating the user closing the db themselves rather than actually remotely booting them.

 

[Porsche996GT2]

Question for Quehay:

Do I need to select a reference for this code? I tried to incorporate this in one of the databases we use and I'm getting an "Object does not support this property or method." error. If so, which one do I need to check? I'm using Access 2002 btw. TIA for your help.