Visual Basic coding to show users in MS Access Database

[PJname]

I am interested in knowing if someone has the Visual Basic coding developed that I can include in a MS Access database that shows a listing of users that are logged into a Microsoft Access Database.

I have an automatic timeout after 10 minutes if it is idle, but there may be an occassion where I need to find out who is logged into the database and tell them to logout.

Our "IT" group has a way to do this, but unfortunately they will not allow anyone else outside their group to do this.

Any suggestions greatly appreciated.
or :

 

[PHV]

Take a look here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;198986

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[drctx]

the ldbviewer in the microsoft jet utilities with show you which computers are has the mdb open.
the utilities are at microsofts site below:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;176670

 

[PJname]

Thanks drctx. I used the coding that you suggested, and this is the results that appears in the window...

ShowUserRosterMultipleUsers
COMPUTER_NAME LOGIN_NAME CONNECTED SUSPECT_STATE
GGN-D3GT8C11 Admin True Null
GGN-D3GT8C11 Admin True Null
GGN-D3GT8C11 Admin True Null


It gives me the Computer Name that shows logged into the database, but can you supply the Network Login_name VB coding? I am not a VB code expert so I will need some simple coding.

 

[AlohaMichael]

Code I use all the time to grab the network login name.
Doesn't show who is currently logged in though.

Function GetUserName() As String
 
'Buffer size for the return string.
Const lpnLength As Long = 255
 
'Get return buffer space.
Dim status As Integer
 
'For getting user information.
Dim lpName, lpUserName As String
 
'Assign the buffer size constant to lpUserName.
lpUserName = Space$(lpnLength + 1)
 
'Get the log-on name of the person using product.
status = WNetGetUser(lpName, lpUserName, lpnLength)
 
'See whether error occurred.
If status = NoError Then
    'This line removes the null character. Strings in C are null-
    'terminated. Strings in Visual Basic are not null-terminated.
    'The null character must be removed from the C strings to be used
    'cleanly in Visual Basic.
    lpUserName = Left$(lpUserName, InStr(lpUserName, Chr(0)) - 1)
End If
 
'Display the name of the person logged on to the machine.
GetUserName = lpUserName
 
End Function

 

[SiJP]

PJ..

Does your database use MS Access security (e.g. do you have to login to the DB each time you open it?).

- or -

Are you the DB admin? (do you do development work to this particular DB?)

If the former, then yes, I have some code for that.

If the later, then you will have to develop a module to extract who the current user is on startup (and yes, I have some code for that too).

If neither of these situations apply.. there isn't a way to get the NT username of that PC (that I know of).

------------------------
Hit any User to continue

 

[PJname]

Hello SiJP,

I developed this Microsoft Access database myself. Any enhancements that should be made, I do.

I don't use MS Access Security. I do have security set up on some of the fields on the form that only allows certain individuals to update the fields but this is written in VB coding.

I found the following coding from Tek-tips forum where it pulls the NT logon id and displays on the main menu of the person logging into the database.


'**************CodeStart***************

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long

'-----------------------------------------------------------------------------------------
' This code is used to retrieve the network user name by accessing the API apiGetUserName.
' Created by: Unknown (Found on Dev Ashish web site

http://home.att.net/~dashish/api)

' This code has not been altered in anyway.
' Added to database: 27 dec 1999
' Added by: Richard Rensel
'-----------------------------------------------------------------------------------------

Function fOSUserName() As String
On Error GoTo fOSUserName_Err

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 = ""
End If


fOSUserName_Exit:
Exit Function

fOSUserName_Err:
MsgBox Error$
Resume fOSUserName_Exit
End Function

'*************CodeEnd****************


This code works great.

I am not a VB guru so I am still working my way through it. I will need detail instuctions on how to implement.

Thanks.....

or ;-)

 

[SiJP]

Good to hear you've some working code.. as an alternative to using the API, you can also use:

Environ("username")


Which saves several lines of code... try it..

------------------------
Hit any User to continue

 

[PJname]

Hi SiJP,

Do you have example of your coding that you are refering to in the above message - Environ("username").

I am not to familiar with VB coding so I will need detail instructions:

1) VB coding example

2) Where this coding should be applied

3) Anything else that would be useful.....

Thanks again...

 

[SiJP]

Sure... open MS access and click on the Module tab. Click to create a new module, and paste the following code into that module:

[COLOR=Blue]Public Sub[/color] fDisplayMe()
[COLOR=Blue]On Error GoTo[/color] err_handler

    [COLOR=Blue]Dim[/color] strMessage [COLOR=Blue]As String[/color]
    strMessage = "Hi, my Windows NT login is: " & Environ("UserName")
    
    [COLOR=Blue]Debug.Print[/color] strMessage
    
err_handler:
    [COLOR=Blue]If[color] err.Number <> 0 [COLOR=Blue]Then[/color]
     MsgBox "Error occured: " & err.Number & " - " & err.Description, vbOKOnly + vbCritical
     [COLOR=Blue]Exit Sub
    End If
End Sub[/color]

..Next, open the Immediate Window by pressing Ctrl+G. In Access 2000/2002 the Immediate Window will appear, usually docked to the lower edge of the Visual Basic Editor window. In Access 97 a new window (the Debug Window) will appear, divided horizontally into two sections - use the lower section of this window.

Type in the following line of code in that immediate window, then press return:

Call fDisplayMe()

Et voila..!

------------------------
Hit any User to continue

 

[tinymind]

Here is a link to a useful page with an example of how to use the LDB Viewer in an MS Access 2000 form.

http://www.mvps.org/access/modules/mdl0055.htm

Hope it helps ...

Tiny


Perfection is Everything
If it worked first time we wont be here!

 

[PJname]

I would like to thank everyone for their help.

tinymind, I looked at your suggestion and it looks exactly like what I am trying to capture at least from the brief testing that I did this morning. I will perform more detailed testing later today. Appreciate everyone's help.



"Freedom is the sure possession of those alone who have the courage to defend it."

God Bless America.....

 

[PJname]

tinymind or anyone
Question?


When I was performing further testing, I received the following errors:


Microsoft Jet Database Engine
Error Number: -2147467259
Could not use"; file already in use.

AppUser
File not found: MSLDBUSR.DLL

Any suggestions on how I can fix this problem?


"Freedom is the sure possession of those alone who have the courage to defend it."

God Bless America.....

 

[SiJP]

Where u getting this error?

(*bump* with anyone else )

------------------------
Hit any User to continue

 

[PJname]

Hello SiJP,

Yes,

when I tried looking at a specific MS Access Database using the AppUser.mdb that tinymind pointed me too, this error came up on the screen.

on other databases, the AppUser.mdb works fine. Not sure why getting error on this particular database.

 

[FancyPrairie]

Here's another thread thread702-676860 that has a function that returns a recordset of who's logged on. It also shows you how to set up a form to handle the results and how to setup security.

 

[PJname]

Hi FancyPrairie,

I followed your instructions on the thread that you sent to me but I am getting a Compile error on the name in bold:
Compile error / Sub or Function not defined

This is the code that I copied to a module that I named UserCurrent from your page.....

Option Compare Database
Public Function GetCurrentUserName() As String
On Error GoTo Err_GetCurrentUserName
Dim lpBuff As String * 25
Dim ret As Long, username As String
ret = GetUserName(lpBuff, 25)
username = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)
GetCurrentUserName = username & ""

Exit_GetCurrentUserName:
Exit Function

Err_GetCurrentUserName:
MsgBox err.Description
Resume Exit_GetCurrentUserName
End Function

Any idea on how to correct the error above?

 

[FancyPrairie]

That wasn't my code. Look at the WhosLoggedOn function. Simply create a new module, copy the code from the thread and paste it into you new module.