VBA code to automatically logout database users

[dleiba]

Hey guys,

I was browsing through previous threads and found one regarding automatically logging off database users before maintenance.

The thread is as follows:

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.

'------------
However, I do not comprehend how to manipulate the LogOutStatus setting using the OnTimer Event Procedure.
Also I am not clear as to how all these procedures are activated.

Could someone PLEASE expand on this entire process for me?

Thanks

Dominic

 

[sdimov]

This idea would be feasible if you would like to make changes to the back-end (like adding a table or a field). In that case you would need all users to disconnect from the back-end.
For application maintenance (front-end) I normally use a ini file to copy the latest version from the network locally and by checking the time stamps I also provide version notes through another text file.
This way, the front-end is never touched by the users and they work on a local copy connected to the network back-end.
The user sees the updated version next time they open the ini file to grab the latest version.

Svet Dimov

 

[nat1967]

sdimov,

i am very interested in your response. i support a database with 15 users and need a better way of doing front end updates. currently, i have to visit each machine and load from a disk or network drive. your response says you use an ini file to accomplish this task automatically. i have never worked with an ini file. can you give me an example of how to accomplish this update "automatically"? thanks in advance.

Nathan (eco1996)

 

[dleiba]

Svet,

Thanks for responding.

I was beginning to wonder whether I had asked a silly question.

One thing to point out is that my database is not split into a Front End and a Back End.

Would an automatic logoff still be possible?

Also, I am still clueless with regards to how the OnTimer Event Procedure code for the syntax above would work.

Thanks again for responding!

Dominic

P.S.
Feel free to send responses to dsleiba@capital-credit.com

 

[klasse]

Hi,

I tell you what I have done, 'cause I think it may help you.
I have used the ideas cited above by you! (Many thanks).

My database is not splitted into front end and back end, either.

The process:
When the database is opened, it asks for username and password, and then I keep track on a hidden form (frmtrack) of the loginname(txtlogin).
This hidden form, has a timer set to 10 seconds.

++++++++++++++++++++++++++++++++++++++++++++
When I start the database, I do:
set RSTuserstatus = currentdb.openrecordset("USER",dbopentable)
with RSTuserstatus
RSTuserstatus.movefirst
while not rstuserstatus.eof
if !LOGINNAME = frmtrack.txtlogin then
let !USER_STATUS = 1 ' user is logged
end if
RSTuserstatus.movenext
wend
++++++++++++++++++++++++++++++++++++++++++++

++++++++++++++++++++++++++++++++++++++++++++
And then I open frmtrack where as the timer is 10 seconds, every 10 seconds it will do:

set RSTmyname = currentdb.openrecordest("SELECT USER_STATUS WHERE LOGINNAME = '" & frmtrack.txtlogin, dbopendynaset)
with RSTmyname
rstmyname.movefirst
while not rstmyname.eof
Select case !USER_STATUS
Case 2
put messagehere
change USER_STATUS to 3
Case 3
change USER_STATUS to 0 (not logged)
quit the application
Case else
do nothing
End select
rstmyname.movenext
wend
end with
++++++++++++++++++++++++++++++++++++++++++

On the other hand, I have a separate form, where I see all machine names connected to the database.
A button there to log out users will do:
+++++++++++++++++++++++++++++++++++++++++++
set RSTtolog = currentdb.openrecordset("USER", dbopentable)
with RSTtolog
RSTtolog.movefirst
while not RSTtolog.eof
if !USER_STATUS = 1 (logged) AND !LOGINNAME <> frmtrack.txtlogin (is not my name) then
RSTtolog.Edit
let !USER_STATUS =2 (check out note)
RSTtolog.Update
End if
RSTtolog.Movenext
wend
end with
++++++++++++++++++++++++++++++++++++++++++++

* Note: this line is the one that activates the whole thing. When frmtrack checks the status and finds that it is 2 it will display the message, change status to 3 and in 10 seconds (the timer), quit the application.

I supposse you can set the timer also from this last code, instead of seting it in the frmtrack.
Forms!frmtrack.timerinterval =10000

Hope it is clear enough?

Rgds,

Klasse