current user name, login time and logoff time

[cooltom]

Hi,
I want to have a table in which i can automatically save the current user name, login time and logoff time. Can anyone tell me method of doing this. Any code (or refrence website) will be be a great help...
Apart from that does access mantains a event log for the errors or anything (like current user name, login time and logoff time)......

Thank you in advance

From,
OP
op609@hotmail.com

 

[SC]

OP,

Set up a table called tblUserLog. This should have three fields. Username, LoginTime and LogOutTime.

The following code should be used in the On Open event of an opening form/Switchboard (i.e. something that is opened when a user opens the database)

Dim username As String
Dim logintime As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblUserLog", dbOpenDynaset)
rst.AddNew
username = Environ$("username")
logintime = Now()
rst!UserId = username
rst!Time = logintime
rst.Update
rst.Close

Then the on Close event or and Exit command button etc. The following code can be used.

Dim username As String
Dim logouttime As String
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
username = Environ$("username")
strSQL = "SELECT tblUserLog.UserID, tblUserLog.timeout FROM tblUserLog WHERE (((tblUserLog.UserID)= '" & username & "'));"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
rst.Edit
logouttime = Now()
rst!logouttime = logouttime
rst.Update
rst.Close

If Environ$("Username") doesn't work try environ$("user")

HTH

SC.

 

[cooltom]

Thanks but this code is for Access 97.. i am using access 2000. when i use this code in 2000, it gives a error on "Dim db As DAO.Database"
can u help me with it !!

From,
OP

 

[jwindon]

In VB, goto tools, references and check off Microsoft DAO 3.6 Object Library.

 

[pcs]

jwindon's post should get you past the error.

but SC's code might need the following modifications:
-----------------
Set up a table called tblUserLog. This should have three fields. Username, LoginTime and LogOutTime.
-----------------

think the table should be:
UserID, LogInTime and LogOutTime.

therefore:
rst!Time = logintime
should be:
rst!LoginTime = logintime


hth,
al

 

[moose]

sorry to but in, i have used this code but it doesn't record the user name or logout time in the table, am i missing something?

 

[SC]

Oops!

There is a problem in the Logout piece of code and pcs is right!

replace the strSQL line with this
strSQL = "SELECT tblUserLog.UserID, tblUserLog.logouttime FROM tblUserLog WHERE (((tblUserLog.UserID)= '" & username & "'));"

Moose - I'm not sure why It didn't pick up the Username. It may be that your network settings are different. I've got some code somewhere that finds out what environ$ values that can be used on any setup. I'll dig this out and post it later.

Cheers,

SC

 

[SC]

The code to find out all the environ variables that can be used is as follows:

Public Function getenviron()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim rst As DAO.Recordset
Dim n As Integer

Set db = CurrentDb()
Set tdf = db.CreateTableDef("tblEnvironDetails")
With tdf
.Fields.Append .CreateField("EnvironValue", dbText)

db.TableDefs.Append tdf

End With

Set rst = db.OpenRecordset("tblEnvironDetails", dbOpenDynaset)
n = 1

Do Until Environ = ""
rst.AddNew
rst!environvalue = Environ
rst.Update
n = n + 1
Loop
rst.Close
db.Close

End Function

This will create a table tblEnvironDetails. You should find something in there that you can use. Then you need to replace Environ$("username") in the previously posted code with whatever is the network username with your set up. It may be Logname, NWusername or user.

HTH.

SC.

 

[The_Doc_Man]

If you had secured this with a workgroup, your users would have had to log in, at which time the CurrentUser function would tell you who it is.

We did our usage auditing by having an opening splash screen that, in its Form_Open event, writes an entry to an audit table (with username, "Login", and a timestamp, of course.) Then you have to click an OK button to make our splash screen go away. (It has notices on it that we wanted folks to read, so we decided to make it annoying that way...)

When you click the GO_AWAY button, the form looks like it closes - but it really doesn't. Instead, it minimizes itself and goes invisible. But it is still there, lurking in the background. When the user decides to close the database, all open forms are exited, so in the Form_Exit event, we log another entry to the audit table with the time, date, user, and a "Logout" event.