usernames and times

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I setup users, groups, and passwords. Is there a way to create a table that
would be able to act as a log, where it would be able to record what user
logged in and what time they did?
 
I do this with a simple action query where I put in the currentuser in a
table.
The fields of the table are: logID, sUsername, dTime, dDate

The query runs in the autoexec macro and thus runs on every startup.

But I'd be interested in hearing about how others are doing it.
 
How do you put the currentuser in a table? Does it automatically create it or
did you make it ?
 
I setup users, groups, and passwords. Is there a way to create a table that
would be able to act as a log, where it would be able to record what user
logged in and what time they did?
Click to expand...

A little similar to Jesper, but here is what I do.

1. Create a new table called tblUserLog with the following fields:
LogID - Autonumber (Primary Key)
ProgramUser - Text
TimeIn - Date/Time
TimeOut - Date/Time

Save the new table.

2. I have a switchboard-type form that is the first form open via
Tools | Startup and is always open. You could do something similar
by creating a small hidden form that is always open behind the scenes.

3. In the Declarations area of the Switchboard form code module I have this:
Public LogInT As Date

(That could just as easily be put in a standard module as well)

4. In the Form's Load event I have this code:
(A reference to the DAO object library must be set)

'*************Code Start***************
Private Sub Form_Load()
On Error GoTo ErrorPoint

' Record this Login to tblUserLog
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblUserlog")
With rst
.AddNew
.Fields("ProgramUser") = CurrentUser()
.Fields("TimeIn") = Now()
LogInT = !TimeIn
.Update
End With

ExitPoint:
' Cleanup Code
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Sub

ErrorPoint:
' Unexpected Error
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Sub
'*************Code End***************

5. In the Form's Unload event I have this code:

'*************Code Start***************
Private Sub Form_Unload(Cancel As Integer)
On Error GoTo ErrorPoint

' Record the time out in tblUserLog
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQLUser As String

Set dbs = CurrentDb()

strSQLUser = "Select * From tblUserlog " _
& "Where ProgramUser = '" _
& CurrentUser() & "' And TimeIn = #" & LogInT & "#"

Set rst = dbs.OpenRecordset(strSQLUser)
With rst
If Not (.EOF And .BOF) Then
'Record was found
.Edit
.Fields("TimeOut").Value = Now()
.Update
End If
End With

ExitPoint:
' Cleanup Code
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Sub

ErrorPoint:
' Unexpected Error
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Sub
'*************Code End***************

This has worked fine for me for many years.
I can then create various reports off this table information.

Side note - Make sure you grant your custom groups appropriate
permissions to this table and/or change the code to use a saved
RWOP query.

Hope that helps,
 
TimeIn - Date/Time
TimeOut - Date/Time
Click to expand...

Good idea to log both. Other than that this is what I'm doing.
It's a requirement in my database that a log is kept of acitivy.
My users used their first names as usernames thus we know who made an entry.

I've thought of what to do if the users have non-descriptive usernames such
as 5&c#x and what I'd do to find you what person was in the database. The
only thing I've thought of was that I had to have a table matching people's
real names with the usernames. This however forces me to have the usernames
in the database and maintain both the table and the workgroup file.
If anyone has any thoughts on this I'd like to know.

By the way Jeff, your paper on security is great!
The official faq scared me at first be is much less frightning now, so thank
you for that :-)
 
"Jesper Fjølner" <jesperfjoelnerAThotmailDOTcom> wrote in message

Hi Jesper,

Comments below.....
Good idea to log both. Other than that this is what I'm doing.
It's a requirement in my database that a log is kept of acitivy.
My users used their first names as usernames thus we know who made an entry.
Click to expand...

I like having the log out time as well because it could be used to
trace the source of a problem if that user is closing Access improperly.
There would be no Log out time for that person. You could also
easily record the computer name as well in this routine to trace corruption
problems that could be occurring.
I've thought of what to do if the users have non-descriptive usernames such
as 5&c#x and what I'd do to find you what person was in the database. The
only thing I've thought of was that I had to have a table matching people's
real names with the usernames. This however forces me to have the usernames
in the database and maintain both the table and the workgroup file.
If anyone has any thoughts on this I'd like to know.
Click to expand...

Hummm...I'm not so keen on this idea. If the user's login name is Bob or 5&c#x
you would still be able to know "who" it is, correct? I honestly see more problems
than benefits by having a table of user names. That also just presents another
crack in security that could be exploited. If your users are completely unaware of
the logging code, then why would it matter if the user names are "easily readable"
names?
By the way Jeff, your paper on security is great!
The official faq scared me at first be is much less frightning now, so thank
you for that :-)
Click to expand...

At the risk of sounding like a complete idiot (I know, big stretch there), to
what paper are you referring to??? I was not one of the authors of the
security FAQ.
 
I like having the log out time as well because it could be used to
trace the source of a problem if that user is closing Access improperly.
There would be no Log out time for that person. You could also
easily record the computer name as well in this routine to trace corruption
problems that could be occurring.
Click to expand...

Good point.
At the risk of sounding like a complete idiot (I know, big stretch there), to
what paper are you referring to??? I was not one of the authors of the
security FAQ.
Click to expand...

Yes, sorry, my mistake. I thought I remembered your name from this paper
http://www.geocities.com/jacksonmacd/
but that's Jack MacDonald. Good though.
 
How do you make sure a form is always open? Is there a property for this? I
have a hidden form that opens on Autoexec, but it has closed for some users
after they make entries on another form. I need to make sure it stays open.

Thanks, Mary
 
Mary said:
How do you make sure a form is always open? Is there a property for
this? I have a hidden form that opens on Autoexec, but it has closed
for some users after they make entries on another form. I need to
make sure it stays open.
Click to expand...

I've never had that problem. Figure out what is closing it. Forms don't close
by themselves.

You could put...

If MyCloseVariable = False Then Cancel = True

....in the form's Unload event and that would prevent the form from closing
until/unless you set the public variable MyCloseVariable to True. However; I
would investigate the problem further first as this should NOT be necessary.
 
Back
Top