Hi Mike,
Each user has his/ her own log in name and password.
It sounds like you are using Access User Level Security (ULS) instead of a
"roll-your-own" method of security. Is this correct? If the answer is yes,
you should be able to interrogate the value of CurrentUser, adding this value
along with the current date & time to a logins table. If you are not using
ULS, then CurrentUser will simply return "admin".
To get this to work, either call a function from an autoexec macro or use
code behind a startup form that determines the currentuser and runs an append
query to append this data into your logins table. Here is how I do this for a
database where I use the "roll-your-own" method in leiu of ULS:
frmDisclaimer
Includes a label with the following caption:
This Data Management System is intended for the sole use of authorized users
of The Boeing Company. Unauthorized access, use or adaptation of this
database, or any of the information contained within, is strictly prohibited.
Such action may result in disciplinary action and/or possible legal action.
This disclaimer form includes two command buttons: one to allow the person
to bail out, ie. DoCmd.Quit, and the other one named cmdOK. The code behind
cmdOK is as follows:
Option Compare Database
Option Explicit
Private Sub cmdOK_Click()
On Error GoTo ProcError
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb()
If Len(gstrUserName) = 0 Then 'Populate global variable, if it happens
to be null.
GetWorkstationInfo
End If
strSQL = "SELECT Person_rk, NTUserID, blnActivePerson, blnDBAdministrator
" _
& "FROM Person " _
& "WHERE NTUserID = '" & gstrUserName & "' AND blnActivePerson <> 0"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
If rs.RecordCount = 0 Then 'We have an invalid user
MsgBox "Please contact {Enter Name Here}" & vbCrLf & _
"to become a valid user of this database.", _
vbCritical, "Invalid User Credentials..."
cmdExitApp_Click
Else
'Store pkUserID in global variable
glngUserID = rs!Person_rk
gvarAdmin = rs!blnDBAdministrator
'Record last login
strSQL = "INSERT into tblLogins (fkPersonID, LastLogin) " _
& "VALUES (" & glngUserID & ", #" & Now() & "#);"
'Debug.Print strSQL
db.Execute strSQL, dbFailOnError + dbSeeChanges
'Store glngLoginID in global variable (used later to record
'logout time in frmSwitchboard Form_Close procedure.
glngLoginID = DMax("pkLoginID", "tblLogins")
'Open switchboard form and close this form
DoCmd.OpenForm "frmSwitchboard"
DoCmd.Close acForm, Me.Name
End If
ExitProc:
'Cleanup
If Not rs Is Nothing Then
rs.Close: Set rs = Nothing
Set db = Nothing
End If
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in cmdOK_Click event procedure..."
Resume ExitProc
Resume
End Sub
The GetWorkstationInfo function referenced above comes from here:
http://support.microsoft.com/?ID=210088
This function is included in a stand-alone module. The global variable
glngLoginID is declared in this module as well.
If you are using ULS, this could be simplified. Instead of calling the
GetWorkstationInfo function, I think you could simply have something like
this, although consider this "air code", as I have not taken the time to test
it:
Private Sub cmdOK_Click()
On Error GoTo ProcError
Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb()
'Record last login
strSQL = "INSERT into tblLogins (UserName, LastLogin) " _
& "VALUES ('" & CurrentUser & "', #" & Now() & "#);"
'Debug.Print strSQL
db.Execute strSQL, dbFailOnError + dbSeeChanges
'Store glngLoginID in global variable (used later to record
'logout time in frmSwitchboard Form_Close procedure.
glngLoginID = DMax("pkLoginID", "tblLogins")
'Open switchboard form and close this form
DoCmd.OpenForm "frmSwitchboard"
DoCmd.Close acForm, Me.Name
End If
ExitProc:
'Cleanup
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in cmdOK_Click event procedure..."
Resume ExitProc
Resume
End Sub
My tblLogins table currently includes the following fields:
pkLoginID Autonumber primary key
fkPersonID Number (Long Integer)
LastLogin Date/Time
LastLogout Date/Time
blnAutoLogout Yes/No
The blnAutoLogout is a field I use to track users kicked out automatically,
after 20 minutes of inactivity. In your case, since you appear to be using
Access ULS, you could rename the fkPersonID field to UserName and change the
data type to Text.
To get all this to work smoothly, you should have a split database. Please
see this page if you are unfamiliar with the concept of splitting:
http://www.access.qbuilt.com/html/gem_tips1.html#SplitDB
Pay particular attention to the last paragraph "Beware splitting a secure
database with the wizard". Each user should have a copy of the front-end (FE)
application on their local hard drive. You may find this article helpful as
well:
Implementing a Successful Multiuser Access/JET Application
http://www.accessmvp.com/TWickerath/articles/multiuser.htm
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
