Solved: proceedure to check who is logged in at midnight and change a value in a table

[OTWarrior]

on our network, there are people leaving their pc logged on overnight, and logged into our database (which means i can't do a compact and repair in the morning). We use a frontend for the program, and a backend for the data.

how would i go about creating a script that is held on the backend (on the server) that runs every day at midnight, and for every user that is logged in, changes a specific value in a certain table, and outputs the name to a text file.


I would like to try and code this myself, but just need a head start (or even to know if this is possible)

[OBP]

Is it possible to have a Form in your backend?
Do the users leave their Computers on as well?
If you can have the form in the backend then set the forms on timer event to check the date and time say every 10 minutes. If the time is greater than 00:00 and the date is greater than the date held on file then run your code to save the data and then reset the date to the new day.
If the users leave their computers switched on, then it it is better to have the Form in the Front end as it can actually log them out of Access and he database.

[OTWarrior]

I was thinking of having the form in the backend, that way it will always run (we have a form on the backend that we can use to log everyone out, but it only works if the computers are "open", if they are locked it will not work (guessing as the system is in idle and the code doesn't run until they unlock the computer)

I like the idea of having the time/date greater than a certain value. I will have to go by the system clock for that, and i could just save their details in a table on the backend.

thank you for that...now....to code :)

[OTWarrior]

my sql is rather basic, but can anyone tell me why this doesn't work?

Dim sql As String
Dim count As Boolean
If Time() > #10:00:00 PM# And count <> True Then
count = True
sql = sql & "INSERT INTO tbl_ADMIN_UserOvernight (txtUser) "
sql = sql & "SELECT '" & Me.User & "';"
DoCmd.RunSQL (sql)
sql = sql & "INSERT INTO tbl_ADMIN_UserOvernight (dteDateOccured) "
sql = sql & "SELECT '" & Date & "';"
DoCmd.RunSQL (sql)
ElseIf Time() > #10:40:01 AM# And Time() < #10:45:40 AM# And count <> True Then
count = True
sql = sql & "INSERT INTO tbl_ADMIN_UserOvernight (txtUser) "
sql = sql & "SELECT '" & Me.User & "';" & "Test"
DoCmd.RunSQL (sql)
sql = sql & "INSERT INTO tbl_ADMIN_UserOvernight (dteDateOccured) "
sql = sql & "SELECT '" & Date & "';"
DoCmd.RunSQL (sql)
MsgBox "done"
Else
End If

nb: the elseif section is for testing only, and the formtimer fires every 3000

[Oorang]

Hi,
This example assumes the existence of a table called "usys_UserLog" with two fields, "UserName" (text) and Time Logged In (date/time).
Sub FlagUsers()
'http://support.microsoft.com/kb/198755
Const strUserRosterSchema_c As String = "{947bb102-5d43-11d1-bdbf-00c04fb92675}"
Const lngFldsUserName_c As Long = 1
Const lngMaxUsrs_c As Long = 255
Const lngOffset_c As Long = 1
Dim cn As ADODB.Connection
Dim rsUsrs As ADODB.Recordset
Dim rsULog As ADODB.Recordset
Dim strUsr As String

Set cn = Access.CurrentProject.Connection
Set rsUsrs = cn.OpenSchema(adSchemaProviderSpecific, , strUserRosterSchema_c)
Set rsULog = New ADODB.Recordset
rsULog.Open "usys_UserLog", cn, adOpenDynamic, adLockOptimistic
Do Until rsUsrs.EOF
strUsr = rsUsrs.Fields(lngFldsUserName_c).Value
strUsr = VBA.Left$(strUsr, VBA.InStr(strUsr, vbNullChar) - lngOffset_c)
rsULog.AddNew Array(0, 1), Array(strUsr, VBA.Now)
rsUsrs.MoveNext
Loop
rsUsrs.Close
rsULog.Update
rsULog.Close
End Sub

[OTWarrior]

If Time() > #10:00:00 PM# Then

CurrentUser = GetUserName() ' Me.User
dteCheckToday = Nz(DLast("dteDateOccured", "tbl_ADMIN_UserOvernight", "[txtUser] = '" & CurrentUser & "'"), #1/1/1900#)
If dteCheckToday < Date Then
sql = sql & "INSERT INTO tbl_ADMIN_UserOvernight (txtUser, dteDateOccured) "
sql = sql & "SELECT '" & CurrentUser & "', "
sql = sql & "'" & Date & "' ;"
DoCmd.RunSQL (sql)
End If
End If

this code seems to do the trick, thanks for your suggestion though Oorang, I will try it out later and let you know if it works better :)