get list of users for a linked (BE) file; not the current FE

[markjkubicki]

I have a project with two (2) back-end files.


And have hobbled together the below code to check if anyone else has either of those files open (so that, if no one does, it can be update...) Admittedly, there are a few, but significant lines, that I have no comprehension of at all =:- (

What I did however, was create code that generates who has the current (Front End) project open, not either of the linked (BE) files (...would only need to know who has one of the BE files open at a time; never both)

...return to the internet I go; and now my head spins (probably because the discussions are 2-steps (not 1) above how much i know / understand...)

Would you direct me an how to adjust the code to generate a list of users for the Back-End files (plz) ?

Sub ShowUserRosterMultipleUsers()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim db As Dao.Database
    Dim tbl As TableDef, fld

    Set cn = CurrentProject.Connection
    Set db = CurrentDb
    Set tbl = db.CreateTableDef("Users")
    Set fld = tbl.CreateField("User", dbText)

    ' 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 database.
    gsMsgText = ""
    Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, _
        "", rs.Fields(2).Name, rs.Fields(3).Name
    
    If Not rs.EOF And rs.BOF Then
        rs.MoveFirst
    End If
    
    While Not rs.EOF
        Debug.Print rs.Fields(0), rs.Fields(1), rs.Fields(2), rs.Fields(3)
        If Len(gsMsgText) > 0 Then
            gsMsgText = gsMsgText & vbCrLf
        End If
        gsMsgText = gsMsgText & Replace(Trim(rs.Fields(0)), Chr(0), "") & "  "  _
           & Replace(Trim(rs.Fields(1)), Chr(0), "")
        rs.MoveNext
    Wend

    If Len(gsMsgText) = 0 Then
        gsMsgTitle = "LIST OF ACTIVE USERS"
        gsMsgText = "no one else is using the file"
    End If
    gsMsgResponse = MsgBox(gsMsgText, vbInformation, gsMsgTitle)
End Sub

as always,
with appreciation in advance,
m.

[Micron]

I don't think I agree with your approach. If any user logs in, IMO you should be storing that fact in one table in either BE. As long as you find any record in that table, you don't update either BE. The result of your test for one or the other being free to update (as you seem to be doing) can change from false (not open) to true in a heartbeat. The outcome of that could be be trivial or quite serious. That being said, if something causes an abnormal fe shutdown, their login record can remain, causing you to think they are logged in. Easy enough to remedy by them logging in and out again. Some might prefer to look in the BE folder for the laccdb or ldb file to indicate free or not, but that's not foolproof either.

An answer to your final question could be as simple as counting the logged in users, assuming you have such a table. If not, you should?

[June7]

Does each user run their own copy of frontend?

[orange]

A common approach to performing maintenance on a backend is to force users off the system gently. Typically a message that provides a warning of 3 or 5minutes; then 1 minute... Much better received if there are some procedures and communications in advance with some training/awareness session.

You may get some ideas from this link. Or this other source from Australia.

[markjkubicki]

each user has their own Front End
of the 2 BE files,
- for one of them, all of the users link to the same file and remain linked to it for their full session (i do have a gentle way of flagging to the users that they are about to be booted out when i need to do maintenance)
- for the other, any user can link to one (and only one at a time) of a multitude of BE files; and multiple users can link to the same file at the same time (and they do.)

each users' FE lives on their own desktop...
of the 2 BE, the one by used concurrently by all of the users lives in a shared folder on the office's server;
the other BE's live "all over the place" (generally on the server, but not always; and when they do it can be in any one of many, many folders)

[markjkubicki]

i do not have such a file, but i will tomorrow
i am curious about this approach.

so... if i understand correctly, I'm creating a record of each user when they log in and what files they are logged into?
each time they login, code will search for any remaining ghosts of their previous login and delete that record before creating a new one?

(it think it might be good to also capture which PC they are logged into at the time, but this may not be necessary and serve no other purpose than complicating the code -thoughts?

m.

[markjkubicki]

nice links !!! thnx.

m.

[Micron]

i do not have such a file, but i will tomorrow
i am curious about this approach.

If you're referring to my post, every time a db opens a lock file is created (unless it's opened exclusively) so you probably do "have" such a file? My comment was about knowing whether or not anyone was logged in, thus potentially locking any of the be files, thus not attempting to do any maintenance. Not sure if maintenance is the goal or just data updates. Regardless, my point was about not doing maintenance if anyone is logged into anything, and that could be determined with a login table. As I said though, it's not bulletproof.

Aside from all that, if you still want a snapshot of who has a connection to any of several possible be files I don't know how to do that. Would be interesting to see a solution for that, but it but I know I'll never use it.