Creating A User Log 1

[mordja]

Hi,

I have created a user log by adding the users system ID to a table when they log in and deleting when they log out. The code that I used for the user ID is

This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

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

The insert statement is executed on the on_open event of the startup form and the delete statement on the on_close event of the startup form. This all works great except when

1) the startup is bypassed
2) the database is crashed out of or some other abnormal close occurs

Problem one can be disabled by turning of the AllowBypass property of the database. Any suggestions about what I can do for problem 2 ?

Thanks

Mordja

 

[earthandfire]

If the UserID is in the log when the user logs in then it is probably fair to assume that the previous session closed abnormally. You could then ask the user for confirmation and possibly store that in an error log.

Hope this helps.

 

[mordja]

earthandfire,

True, but from an admin perspective If I want to be able to check who has logged in and when that does not help.
Is there another way that I could do this ? Is there anyway to tell if and when access closed abnormally?

Thanks

Mordja

 

[FancyPrairie]

If all you are trying to do is to determine who is currently logged on to the system, then the following function will tell you that.

I use the function below to tell me who's logged in and then join the results to my UserProfile table. When my application starts up I check to see if the user is "registered" (i.e. record exists in UserProfile table). If the user is not registered then I popup a form and ask the user to complete the form. The form contains information such as, user's name, phone number, office location, and form they want to goto at startup. Note that once the user "registers", they will not be asked to do so again.

So, via the function below, I can display who is currently logged in to my database, their phone number (in case I need to contact them), their office location, and whatever information I may want to know about the user.

'+***************************************************************************************
'*
'*  Sub:        WhosLoggedOn
'*
'*  Author:     FancyPrairie
'*
'*  Date:       December, 2001
'*
'*  Function:   This routine will determine who is logged on the the database specified
'*              by the caller (generally it should be the Workgroup database).
'*
'*              This routine will return the following info in the Recordset passed by
'*              the Caller:
'*
'*              rst.Fields(0).Name = "Computer_Name"    (Char:    Name of the computer)
'*              rst.Fields(1).Name = "LOGIN_NAME"       (Char:    Name of user whos logged in)
'*              rst.Fields(2).Name = "CONNECTED"        (Boolean: True if Connected)
'*              rst.Fields(3).Name = "SUSPECT_STATE"    (Integer: Null if not suspect)
'*
'*  Arguments:  strWorkgroup (string)
'*              ---------------------
'*              This string contains the path (and name) of the database you want to
'*              see who's logged in. Usually you would check the workgroup file.
'*              (Example:  "\\server\ShareName\TheWorkgroup.mdw"
'*
'*              rst (ADODB.Recordset)
'*              ---------------------
'*              This recordset will be returned to the caller.  It will contain the
'*              names of the computers that are logged on to "strWorkgroup" (see the
'*              description of the recordset above).
'*
'*              NOTE:  This routine will create the recordset and populate it.
'*
'*              varSortField (variant - Optional)
'*              ---------------------------------
'*              This variable indicates which field you want the recordset sorted by.
'*              If this argument is not passed, the recordset will not be sorted.  The
'*              possible values for this variable are:
'*                 -1 = Don't sort the data
'*                  0 = Sort by rst.Fields(0)   (Computer_Name)  (DEFAULT)
'*                  1 = Sort by rst.Fields(1)   (Login_Name)
'*                  2 = Sort by rst.Fields(2)   (Connected)
'*                  3 = Sort by rst.Fields(3)   (Suspect)
'*
'*              varAscDesc (variant - Optional)
'*              -------------------------------
'*              Indicates how the data is to be sorted.  The 2 possible values are:
'*                  "ASC"  = Sort Ascending (DEFAULT)
'*                  "DESC" = Sort Descending
'*
'*  Example:    The following is an example of how to call this routine.  The call shown
'*              will return all of the computers logged on to "\\server\ShareName\TheWorkgroup.mdw"
'*              and sorted by "Computer_Name" in Ascending order.
'*
'*                  Dim rst As ADODB.Recordset
'*
'*                  Call WhosLoggedOn("\\server\ShareName\TheWorkgroup.mdw", rst)
'*
'+***************************************************************************************
CODE
Option Compare Database
Option Explicit

Public Sub WhosLoggedOn(strWorkgroup As String, _
                        rst As ADODB.Recordset, _
               Optional varSortField As Variant = 0, _
               Optional varAscDesc As Variant = "Asc")

'********************************
'*  Declaration Specifications  *
'********************************

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    
    On Error GoTo ErrHandler

'*************************
'*  Open Workgroup file  *
'*************************

    cn.Provider = "Microsoft.Jet.OLEDB.4.0"
    cn.Open "Data Source=" & strWorkgroup

    Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
    , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

'*********************************
'*  Create Fields for Recordset  *
'*********************************

    Set rst = New ADODB.Recordset

    rst.Fields.Append rs.Fields(0).Name, adVarWChar, 32
    rst.Fields.Append rs.Fields(1).Name, adVarWChar, 32
    rst.Fields.Append rs.Fields(2).Name, adBoolean
    rst.Fields.Append rs.Fields(3).Name, adInteger
    
'*************************************************************************
'*  Loop thru Recordset and add Computer Name, etc. to user's recordset  *
'*************************************************************************

    rst.Open
    
    While Not rs.EOF
        
        rst.AddNew
        If (Not IsNull(rs.Fields(0))) Then rst.Fields(0) = rs.Fields(0)
        If (Not IsNull(rs.Fields(1))) Then rst.Fields(1) = rs.Fields(1)
        If (Not IsNull(rs.Fields(2))) Then rst.Fields(2) = rs.Fields(2)
        If (Not IsNull(rs.Fields(3))) Then rst.Fields(3) = rs.Fields(3)
        rst.Update
        
        rs.MoveNext
    Wend

    If (varSortField <> -1) Then
        rst.Sort = rst.Fields(varSortField).Name & " " & varAscDesc
    End If
    
'********************
'*  Exit Procedure  *
'********************
        
ExitProcedure:

    Set rs = Nothing
    Set cn = Nothing

    Exit Sub

'****************************
'*  Error Recovery Section  *
'****************************
        
ErrHandler:

    Err.Raise vbObjectError + 20100, "Error occcurred in function WhosLoggedOn", "Error Number: " & Err.number & vbCrLf & vbCrLf & "Error Description: " & Err.Description
        
    Resume ExitProcedure

End Sub

 

[mordja]

FancyPrairie,

I ran your module but for some reason it only returned the Computer name, the other three fields were blank ? When calling the function from within the VBE it returns my Computer name twice, does it count me as being logged in twice because I am in the VBE and the database ?

Thanks

Mordja

 

[FancyPrairie]

Not sure what you did wrong. I just copied and pasted the function WhosLoggedOn into a database, added and ran the following code and it worked fine. I tried it without passing the name an mdw file (workgroup), but rather the name of an unsecured database, thinking that might be your problem. But it returned "Admin" as the user name.

Function testme()

    Dim rst As ADODB.Recordset
    
    WhosLoggedOn "c:\JustAnoterDatabase.mdb", rst

    rst.MoveFirst

    MsgBox rst.Fields(0).Value
    MsgBox rst.Fields(1).Value
    
End Function

 

[mordja]

FancyPrairie,

I tried your test function and all fields were printed. The only difference was that I was trying to pring them on the one line and for a reason unknown to me, maybe you can explain, only rst.Fields(0) printed out.

MsgBox rst.Fields(0).Value & " " & rst.Fields(1).Value & " " & rst.Fields(2).Value & " " & rst.Fields(3).Value

Thanks

Mordja

 

[FancyPrairie]

Yeah, the same thing happened to me. But when I checked the values in debug, they were there. So I printed them out with different msgboxes. When that worked, I just left it at that. Didn't bother to figure out why it doesn't work in one msgbox. It doesn't make sense.

 

[mordja]

Ok, any ideas as to why my hostname is logged in three times, twice as my username and once as admin ? Also where can more information about the available schemas ?

Thanks

Mordja

 

[VBACT]

A related question to this thread.....

How do I remove the ability of users from holding down the SHIFT key when firing up a MDE version of a database. In other words I want to disable AllowByPass key

Would appreciate any help.....

 

[mordja]

Apologies,


Ok, any ideas as to why my hostname is logged in three times, twice as my username and once as admin ? Also where can more information about the available schemas be found ?


VBACT,

Shiftkey, if the property exists then

CurrentDb.Properties("AllowBypassKey") = False

if not you need to create it

Dim prpChangeByPass As Property

Set prpChangeByPass = CurrentDb.CreateProperty("AllowBypassKey", dbBoolean, False)
CurrentDb.Properties.Append prpChangeByPass

I found this on the net, try searching for a database called ShiftKeyV2000, Im not sure who wrote it, it doesnt really say


' This code is from the Access. Web


but it allows you to turn the shift key property of a another database on and off, which is handy as once you turn it off you need to turn it back on to be able to enter the design.

Mordja