Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

View login ID's of users currently using the DB

How To

View login ID's of users currently using the DB

by  sucoyant  Posted    (Edited  )
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!
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top