There are other FAQ's that show how to view users currently logged in using the computer name, and sometimes that just wont work.

This FAQ will explain how to view the LOGIN ID's of users currently using the database.

The process of how this works goes like this:
Open DB -> Auto loading form gets called -> Grabs Login ID -> Places Login ID into empCurrentlyLoggedIn field in tblCurrentlyLoggedIn table
Close DB -> Auto loaded form deletes your Login ID from tblCurrentlyLoggedIn -> DB closes

We will need a few things to make this work:
Table: tblCurrentlyLoggedIn
   Fields: empCurrentlyLoggedIn (Text datatype)
Code: basfOSUserName
Form: frmShowDatabaseUsers
   Objects:
    List Box - lstEmpName
    Command Button - cmdExit

For this FAQ we will be using some excellent code by Dev Ashish. This code grabs the LOGIN ID of the user:

CODE

******************** Code Start **************************
' 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
'******************** Code End **************************

Place the above code into a new module called "basfOSUserName".

If you are using a form that automatically launches when you open the database, this will be easy. For simplicity I will assume this.

In the "On Open" event of the auto launching form past the following code:

CODE

DoCmd.SetWarnings False

DoCmd.RunSQL "INSERT INTO tblCurrentlyLoggedIn ( empCurrentlyLoggedIn ) " & _
"SELECT fOSUserName() AS Users;"

DoCmd.SetWarnings True

Then in the "On Close" event past this code:

CODE

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblCurrentlyLoggedIn WHERE empCurrentlyLoggedIn = fOSUserName()"
DoCmd.SetWarnings True

Now, you must create a new form called "frmShowDatabaseUsers". Place the 2 items needed on the form from the list at the top of the page.

Set the Row Source Type to "Table/Query" for lstEmpName.
Set the Row Source to "tblCurrentlyLoggedIn".

In the "On Click" event of the cmdExit button place the following code:

CODE

On Error GoTo Err_cmdExit_Click

    DoCmd.Close

Exit_cmdExit_Click:
    Exit Sub

Err_cmdExit_Click:
    MsgBox Err.Description
    Resume Exit_cmdExit_Click

You're done!

Back to Microsoft: Access Modules (VBA Coding) FAQ Index
Back to Microsoft: Access Modules (VBA Coding) Forum
My FAQ Archive

Email This FAQ To A Friend

My Archive

My FAQ Archive