Get number of users currently using an access db

[easyc]

Hi,

I'm a bit of a newb to VBA - I've done lots of work with VBScript but not with Access and VBA. I have a quick question about users access a db:

Get number of users currently using an access db:
------------------------------------------------

I'd like to find out how many users are accessing an Access database and display this info in a textbox/label on a form.

Does anyone know how to do get this info programmatically? and if so would you mind listing any other attributes that are available please.

Thanks for taking time to read!
Clive

 

[bonekrusher]

Run an Audit Trail (search the forum) and then a query by user name.

 

[llkhoutx]

I log each user into a table when they open the database and log them out when they quit the database.

Of course sometimes their FE or the BE crashes and if presently logged in, they appear to be so when they are not. I merely require that they quit and log back in to reintiate their position in the log.

If someone has a better way, I'll use it.

 

[ghudson]

How to determine who is logged on to a database by using Microsoft Jet UserRoster in Access 2002 or in Access 2003

 

[Colby]

ghudson, thank you for the link to the microsoft site on how to tell who
is logged on. I got it to work with little difficulty but it does not return
what I would like. I know, it will only give what I ask for and I am not aksing
the right question
What I would like for it to return is the id the user signed on with. I have a
func that captures this but I am not sure how to incorporate it.

'Returns the network login name

Function fOSUserName() As String

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

'Get the user name from the computer that opened the DB

fOSUserName = UCase(Left$(strUserName, lngLen - 1))
Else

'In case there is no user name

fOSUserName = ""
End If

End Function

Do you or anyone else reading this post know what the next step
could/should be?

Thanks in advance.

 

[Mile-O]

You don't need that function. You can just use Environ("username") now.

i.e.

MsgBox Environ("username")

 

[Colby]

SJ, thanks for the reply; but any chance I could get a little bit of detail?
What I want to happen is when I click on one of my admin forms, I would
get a list box that shows the list of user currently in my app.

Are you saying that I don't need the Micorosoft function or the function that I pasted in prior post?
The below is what I am using now and it rtns the computer _name. I apologize for any clarity issues and or just bad questions. I am pretty much self taught and still learning.

Private Sub Form_Open(Cancel As Integer)

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strListSource As String

Set cn = CurrentProject.Connection

' The user roster is exposed as a provider-specific schema rowset
' in the Jet 4.0 OLE DB provider. You have to use a GUID to
' reference the schema, as provider-specific schemas are not
' listed in ADO's type library for schema rowsets

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

'Output the list of all users in the current database.

While Not rs.EOF
strListSource = strListSource & rs.Fields(0) & "; "
rs.MoveNext
Wend
'get rid of trailing space and semi-colon
strListSource = Left(strListSource, Len(strListSource) - 2)
'set rowsource
Forms!Form2!lstShowUser.RowSourceType = "Value List"
Forms!Form2!lstShowUser.RowSource = strListSource
End Sub