Indentify Users in DB

[GregShah]

Years ago, I posted a question concerning how I could tell who is using our shared database and a member provided me with the following module:

Sub ShowUserRosterMultipleUsers()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset


Dim i, j As Long


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.


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 use to work years ago but I tried in today and nothing happened. I don't have the experience to determine the reason and was hoping someone could provide me with some guidance.

Thanks for the help,

Greg

[Micron]

I can offer alternatives that should be a lot simpler that don't rely on backwards compatibility:
- either use a login form (with all its inherent password management issues) or
- get the Windows user login id and upon user opening db, check if they are in the table of users

The latter allows you to keep out those who aren't registered in the table. When any registered user opens db, save their login date/time/user info as a record. Then usage data is a simple matter of querying tblUsers. If you also record the logout date/time, you can see other user info such as abnormal db closing (log ins without log outs) as well as duration per user.

IMO, a password/login form is really only needed for when more than one user accesses a pc under some other already established login. I suspect there's not much of that going on in the pc world.

Notes:
GUID field/data takes up a lot of memory/disk space in comparison to anything else
In that code, i is a variant, j is a long. It's a common mistake, unless in this case it was intentional. Not properly declaring variable types always leaves this open to interpretation.

EDIT - forgot to say that some like to use TempVars rather than code that gets the user's Windows login name. Also, I passed on troubleshooting your code because I'm unfamiliar with that method, and wouldn't use it.

[Welshgasman]

Well I would search for that guid in the registry. It might have changed.
First though I would walk through the code, line by line and inspect all the variables/objects.

[Micron]

Well I would search for that guid in the registry. It might have changed.
First though I would walk through the code, line by line and inspect all the variables/objects.

You wouldn't scrap it?

[Welshgasman]

You wouldn't scrap it?

Not if I had been using it for quite a while and was confortable with it.

I would be intrigued as to why it stopped working TBH.

[Gicu]

I think the OP wants a list of logged in\active users, not a logging on component. The code provided in post 1 seems to come straight from MS:
https://learn.microsoft.com/en-us/of...on-to-database
First I would check if the required reference (ADO library) is in place.

Here are some other links that might help:
https://www.access-programmers.co.uk...s-2016.295147/
https://stackoverflow.com/questions/...er-the-network
Cheers,

[Micron]

I think the OP wants a list of logged in\active users, not a logging on component.

I think so too, which is why I suggested the table.

When any registered user opens db, save their login date/time/user info as a record. Then usage data is a simple matter of querying tblUsers. If you also record the logout date/time, you can see other user info such as abnormal db closing (log ins without log outs) as well as duration per user.

[Welshgasman]

I have not looked at the guts of this link below, but that code that Vlad linked to worked for me.? It does however mention 2007, which is what I use.

Here it is https://www.access-programmers.co.uk...tabase.295174/

[isladogs]

For info, the latest version of the app in that link is available at: Logged In Users (isladogs.co.uk)