Who's Online?

[CorporateQAinTX]

Hello everyone,
What is the best way to determine who is logged into an Access Dbase through
ULS? I need to know which user is logged in so that I can determine which one
of 7 locations to list as their location id. Is it possible to put
information like that into their security permissions? I'm using the Access
User Level Securities, because it would be easier for someone who has never
done anything with access to update the information if I'm not around. Then
again, it's easier for me too. ; )

TIA,
Garrett

 

[Joan Wild]

The CurrentUser() function will return the Access username. You can then
have a table with the username/location to make the match.

 

[CorporateQAinTX]

I hope someone's watching the group, cause I really need to bounce this one
off of someone....

I found this code on the mvps.org site:
"Sub ShowUserRosterMultipleUsers()
Dim cn As New ADODB.Connection
Dim cn2 As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, j As Long

cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open "Data Source=\\MIDSFDFS001\WORKGROUP\QA\Garrett\QA Database\QA
Dbase Beta.mdb"

cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=\\MIDSFDFS001\WORKGROUP\QA\Garrett\QA Database\QA Dbase
Beta.mdb"

' The user roster is exposed as a provider-specific schema rowset
' in the Jet 4 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.

Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, _
"", rs.Fields(2).Name, rs.Fields(3).Name

While Not rs.EOF
Debug.Print rs.Fields(0), rs.Fields(1), _
rs.Fields(2), rs.Fields(3)
rs.MoveNext
Wend

End Sub".

It works great to determine who's online accross the network. I mapped out
how I'd like this to work, so let me know if it makes sense or if I'm doing
this the hard way.

1. Determine the current computer being used (not sure how, but I'll figure
it out)
2. Run the code above to show all users access the db
3. Use the current computer name to find the specific user on the system at
this moment
4. Open a "Users" table and find the current user on the computer. The
user's Location ID will be listed in this table.

Bada-bing! I know which user is on the db and where. Right?

Does this make sense or am I going in circles? I'd really appreciate some
help on this.

TIA,
Garrett

 

[CorporateQAinTX]

Thank you. That's helping a lot. Next issue though. This may be needed for
multiple forms or reports, so I figured that I would create a Module and call
on it when I needed it. Would that be correct? My code looks like this:

Dim LID As String
LID = DLookup("[LID]", "t_Users", "t_Users![UserName] = " & CurrentUser)

The data I want to pull is based on the UserName which should correspond to
the CurrentUser. The table "t_Users" is set up with only 2 fields, UserName
and LID.

I know this is probably very sloppy, but I'm quickly running out of time.
Thanks for your help so far.

 

[CorporateQAinTX]

I almost forgot.

When I Debug the module as it is now, I get a run-time error '2001'.

It says "You canceled the previous operation."

Any idea what that means? I can't find it in the help file.

 

[Joan Wild]

If you have a form that is opened on startup (even if it's hidden), you can
put a textbox on it with a control source of
= DLookup("[LID]", "t_Users", "t_Users![UserName] = '" & CurrentUser & "'")
Note the single quotes added.

Just refer to this textbox when you need it... Forms!FormName!TextboxName

--
Joan Wild
Microsoft Access MVP

Thank you. That's helping a lot. Next issue though. This may be needed for
multiple forms or reports, so I figured that I would create a Module and
call
on it when I needed it. Would that be correct? My code looks like this:

Dim LID As String
LID = DLookup("[LID]", "t_Users", "t_Users![UserName] = " & CurrentUser)

The data I want to pull is based on the UserName which should correspond
to
the CurrentUser. The table "t_Users" is set up with only 2 fields,
UserName
and LID.

I know this is probably very sloppy, but I'm quickly running out of time.
Thanks for your help so far.

The CurrentUser() function will return the Access username. You can then
have a table with the username/location to make the match.