Puting a date and time into a user log table

[Guest]

I have a user log table of:
NetUserName (e.g. mmiller)
LoginDate
LoginTime
LogoffDate
LogoffTime

I have code that fills it with the login info, which is just an .AddNew.

When the user Exits the app, I -do have- the username available, and I want
to find the user in the table, with their max login date and time, where the
logoff's are null, and then fill those logoffs, with the current date and
time. (I think that would be the correct logic)

I have the query to get the name, and max date and time, but I can't make
that an Update query. If I use ADO, how would I do this (or what is an easy
way to do it?)?

Thanks.

 

[Wayne Morgan]

I would recommend and Update Query. If you can't make the current query and
Update Query because you are using it for something else, then make a copy
of it to create the Update Query.

Example:
strSQL = "UPDATE UserLog SET UserLog LogoffDate = " & Date & " , LogoffTime
= " & Time & " WHERE NetUserName = """ & Me.txtUserName & """ And LoginDate
= " & DMax("LoginDate", "LogTable", "NetUserName='" & Me.txtUserName & "')
And LoginTime = " & DMax("LoginTime", "LogTable", NetUserName='" &
Me.txtUserName & "' And LoginDate=DMax("LoginDate", "LogTable",
"NetUserName='" & Me.txtUserName & "'));"
CurrentDb.Execute strSQL, dbFailOnError 'DAO

There is also an ADO command to execute a query, but I don't know it. Since
user names can't have apostrophes, using single quotes in the DMax
statements shouldn't be a problem.

I think I got all of the quotes in the correct spot. This would be much
simpler if the LoginDate and LoginTime were a single field. The same goes
for the Logoff. You would then use the Now function instead of the Date and
Time functions.

 

[Guest]

Thank you very much Wayne.

I'll try it and let you know.

 

[Guest]

I tried it and even after I strung it out to a single line, I get a syntax
error (at the WHERE statement)

I tried to modify it without success.

 

[Andrew Backer]

Could you post what sql is generated?

 

[Guest]

It's the sql on the prev msg, 6/30, above yours.

 

[Andrew Backer]

What i see is setting a string variable to the result of some series of
calculations that I can't replicate directly. I am interested in
exactly what ends up in strSql, so just a debug.print strSql & copy
should do it.

- Andrew

 

[Guest]

The sql string when 'straightened out', returns a syntax error at WHERE,
expected end of statement.

Line as copied:
strSQL = "UPDATE UserLog SET UserLog LogoffDate = " & Date & " , LogoffTime
" = " & Time & " WHERE NetUserName = """ & Me.txtUserName & """ And LoginDate
= " & DMax("LoginDate", "LogTable", "NetUserName='" & Me.txtUserName & "')
And LoginTime = " & DMax("LoginTime", "LogTable", NetUserName='" &
Me.txtUserName & "' And LoginDate=DMax("LoginDate", "LogTable",
"NetUserName='" & Me.txtUserName & "'));"

 

[Wayne Morgan]

Michael,

Does setting the strSQL statement error or when you try to execute the
statement error? If the latter, please insert a

Debug.Print strSQL

before the Execute statement and copy the result from the Immediate Window
and paste it here. However, if the problem is a quote in the wrong spot, it
will error at the former item.

I went back through all of the quote marks. Try this modification (all on
one line).

strSQL = "UPDATE UserLog SET UserLog LogoffDate = " & Date & " , LogoffTime
= " & Time & " WHERE NetUserName = '" & Me.txtUserName & "' And LoginDate =
" & DMax("LoginDate", "LogTable", "NetUserName='" & Me.txtUserName & "'") &
" And LoginTime = " & DMax("LoginTime", "LogTable", "NetUserName='" &
Me.txtUserName & "'") & " And LoginDate = " & DMax("LoginDate", "LogTable",
"NetUserName='" & Me.txtUserName & "'") & ";"

You may also need to use date dilimiters. If so, the above would be modified
as:

strSQL = "UPDATE UserLog SET UserLog LogoffDate = #" & Date & "# ,
LogoffTime #" = & Time & "# WHERE NetUserName = '" & Me.txtUserName & "'
And LoginDate = #" & DMax("LoginDate", "LogTable", "NetUserName='" &
Me.txtUserName & "'") & "# And LoginTime = #" & DMax("LoginTime",
"LogTable", "NetUserName='" & Me.txtUserName & "'") & "# And LoginDate = #"
& DMax("LoginDate", "LogTable", "NetUserName='" & Me.txtUserName & "'") &
"#;"

 

[Guest]

Wayne,

Thanks for the good info. I shall try it asap and get back to you.

Regarding where the err is appears to to be the former, in the syntax.
Can't move off the code line without the error.

But, I will try all the below.

 

[Guest]

Just got a "bad verb" error on sending a reply to you.
Trying again.and paste it here. However, if the problem is a quote in the wrong spot, it
will error at the former item.<<

In my existing code it was the former, in the syntax check when moving off
of the code line. Doesn't get to execute.

Will try your stuff asap and get back to you.

 

[Guest]

i cany help with your problem but you could help with mine!!

i am trying to log user activity within my DB but i cant get my addnew to
work. please could you paste your code for me to look at

many thanks

EliotK

 

[Guest]

Sure. This one gets machine names, empties my log table, and then adds
whatever records were found in the dll function.

Sub ShowUserRosterMultipleUsers()
On Error GoTo ErrorHandler
Dim cnnMetrics_be As New ADODB.Connection
Dim cnnMetricsLogs As New ADODB.Connection
Dim rstMetrics_be_Users As New ADODB.Recordset
Dim rstMetricsLogs As New ADODB.Recordset
Dim i, j As Long
Dim strTablename As String
Dim strUserName As String

cnnMetrics_be.Provider = "Microsoft.Jet.OLEDB.4.0"
cnnMetrics_be.Open "Data
Source=\\unpiox03pn\SCI_Share\bSolutions_Metrics_Reporting_Initiative\02Test\Metrics_be.mdb"

' The user roster is exposed as a provider-specific schema rowset
' in the Jet 4 OLE DB provider. You have to use a GUID to
' reference the schema, as provider-specific schemas are not
' listed in ADO's type library for schema rowsets
Set rstMetrics_be_Users =
cnnMetrics_be.OpenSchema(adSchemaProviderSpecific, _
, "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

If rstMetrics_be_Users.BOF And rstMetrics_be_Users.EOF Then
' MsgBox "no users were found in the _be database"
GoTo Exit_ErrorHandler
End If

'Open a connection and recordset to The MetricsLog database
cnnMetricsLogs.Open "Provider = Microsoft.Jet.OLEDB.4.0;" _
& "Data
Source=\\unpiox03pn\SCI_Share\bSolutions_Metrics_Reporting_Initiative\01Development\MetricsLogs.mdb"
strTablename = "tblUserRoster"
rstMetricsLogs.Open strTablename, cnnMetricsLogs, adOpenKeyset,
adLockOptimistic, adCmdTable

'Empty the last build of the log

While Not rstMetricsLogs.BOF And Not rstMetricsLogs.EOF
rstMetricsLogs.MoveFirst
rstMetricsLogs.Delete
rstMetricsLogs.Update
Wend

' Output the list of all users in the Metrics_be database, to the
MetricsLogs database.
' Debug.Print rstMetrics_be.Fields(0).Name, "",
rstMetrics_be.Fields(1).Name, _
' "", rstMetrics_be.Fields(2).Name, rstMetrics_be.Fields(3).Name

'Output the list of all users in the current database.

While Not rstMetrics_be_Users.EOF
With rstMetricsLogs
.AddNew
.Fields("COMPUTER_NAME") =
RTrim(LTrim(rstMetrics_be_Users.Fields(0)))
.Fields("LOGIN_NAME") =
RTrim(LTrim(rstMetrics_be_Users.Fields(1)))
.Fields("CONNECTED") = RTrim(LTrim(rstMetrics_be_Users.Fields(2)))
.Fields("SUSPECT_STATE") =
RTrim(LTrim(rstMetrics_be_Users.Fields(3)))
.Update
'move to the next SOURCE record
rstMetrics_be_Users.MoveNext
End With
Wend

Exit_ErrorHandler:
If Not rstMetrics_be_Users Is Nothing Then
If rstMetrics_be_Users.State = adStateOpen Then
rstMetrics_be_Users.Close
End If
Set rstMetrics_be_Users = Nothing

If Not cnnMetrics_be Is Nothing Then
If cnnMetrics_be.State = adStateOpen Then cnnMetrics_be.Close
End If
Set cnnMetrics_be = Nothing

If Not rstMetricsLogs Is Nothing Then
If rstMetricsLogs.State = adStateOpen Then rstMetricsLogs.Close
End If
Set rstMetricsLogs = Nothing

If Not cnnMetricsLogs Is Nothing Then
If cnnMetricsLogs.State = adStateOpen Then cnnMetricsLogs.Close
End If
Set cnnMetricsLogs = Nothing

Exit Sub

ErrorHandler:
MsgBox "Error " & Err.Number & ", " & Err.Description, vbCritical,
"Error in ShowUsers Module: Procedure 'ShowUserRosterMultipleUsers()'."

Resume Exit_ErrorHandler

End Sub

 

[Guest]

i sent you the code, but i got an 'invalid verb' error on sending the reply.
i think it goes thru anyway. if you don't get it, just let me know.

 

[Guest]

thanks

it looks way more than i need, all i want it to do is when they click on a
specific button, i want to record there user name,date,time within a table.
its just to monitor to see if the database is getting much use.

 

[Guest]

I do have code for that but have been having trouble with getting the network
user name (mmiller); I keep getting little square boxes after my name and my
name won't fit in the field, unless I set the field to 256 chars.
Note, the below code calls fOSUsername(), which is included below.
That's the one that was returning (now) one box after my name.
But, here's the code. (1.) is my Log IN code. There is similar code for
logging out:
(1.)
Sub LogUserInRoster()
On Error GoTo ErrorHandler
Dim cnnMetricsLogs As New ADODB.Connection
Dim rstMetricsUserLog As New ADODB.Recordset
Dim strTablename As String
Dim strNTUserName As String

cnnMetricsLogs.Open "Provider = Microsoft.Jet.OLEDB.4.0;" _
& "Data
Source=\\myservername\SCI_Share\bSolutions_Metrics_Reporting_Initiative\01Development\MetricsLogs.mdb"

strTablename = "tblMetricsUserLog"
rstMetricsUserLog.Open strTablename, cnnMetricsLogs, adOpenKeyset,
adLockOptimistic, adCmdTable

' Output the current user logging into the Metrics_be database, to the
MetricsLogs database.
strNTUserName = fOSUserName()
With rstMetricsUserLog
.AddNew
.Fields("NetLoginName") = RTrim(LTrim(strNTUserName))
.Fields("LoginDate") = Date
.Fields("LoginTime") = Time()
.Update
End With

Exit_ErrorHandler:
' If Not rstMetricsUserLog Is Nothing Then
' If rstMetricsUserLog.State = adStateOpen Then rstMetricsUserLog.Close
' End If
Set rstMetricsUserLog = Nothing

If Not cnnMetricsLogs Is Nothing Then
If cnnMetricsLogs.State = adStateOpen Then cnnMetricsLogs.Close
End If
Set cnnMetricsLogs = Nothing

Exit Sub

ErrorHandler:
MsgBox "Error " & Err.Number & ", " & Err.Description, vbCritical,
"Error in ShowUsers Module: Procedure 'LogUserInRoster()'."

Resume Exit_ErrorHandler

End Sub

(2.)
Public Function fOSUserName() As String
'returns the network login name
Dim lngLen As Long
Dim lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen) 'lngLen - 1)
If (lngX > 0) Then
fOSUserName = Trim(Left$(strUserName, lngLen))
Else
fOSUserName = vbNullString
End If
End Function