Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2004
    Location
    Canberra, Australia
    Posts
    13

    Question Unanswered: Who is logged into the Dbase from a user log table?

    Hi,
    Apologies up front for war and peace... Have a small problem which has been bugging me for a couple of days now.
    I have set up a user log table which is updated with the start date and time when a user opens the database. When they close the database it then updates the table with the exit date time. For example:

    Code:
     
    ID	UserID	Start_Date	Start_Time	End_Date	End_Time
    2590	ubh7a	8/02/2008	9:54:05		
    2591	ubh7a					8/02/2008	9:54:35
    2592	ubh7a	8/02/2008	10:29:30
    As you can see the user has logged in and out and then back into the dbase and is still in the dbase.

    I have set up a query that provides a daily log (i.e. Startdate = Date()). However this can grow quite large. Currently to determine who is still in the DBase is a manual scan of this log query. What I would like to do is set up a way this can simplified to users still logged in only. I have thought about looping through the log table using multiple loops and arrayed variables but get stuck on the fact that it will always pull back the first instance.

    Note the DBase doesn,t use Access permission or security (Had too much trouble with it and the code I used - i.e. some code would only work with Admin priveledges) I don't have to use this log if there is a better way.

    Any thougths or help appreciated.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Make your logfile better

    Code:
    ID       UserID     Start_Date     Start_Time     End_Date     End_Time
    2590     ubh7a      8/02/2008      9:54:05        8/02/2008    9:54:35
    2590     ubh7a      8/02/2008      10:29:30
    Then you can just query for those without end details.
    Last edited by StarTrekker; 02-07-08 at 21:49.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Oct 2004
    Location
    Canberra, Australia
    Posts
    13
    [QUOTE=StarTrekker]Make your logfile better

    Thanks for that. It was something I had thought of but was shying away as it means a rewrite of some primary code and forms. Was hoping to code external to them. Oh well here was hoping...

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You can probably still do it without having to recode the log file stuff (with difficulty), but if it was me, I'd recode the logfile creation to make things neater / easier.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    bear in mind that this may not be a true reflection of who is actually in the application. if Access crashes (ok your apps may not but mine and my users do) there is a risk that you may have a ghost logon, where the user didnt' log off for what ever reason. that reason cold be a network failure, power outage somewhere, opther hardware fault, of an app hangs/times out and so on.

    mind you There is a mechanism to query the ldb file.. that should tell you who is connected to the db. it may have similar issues.

    The version I say was in the Access Developer books by Litwin et al, but Im pretty certain its freely available code form Microsoft.

  6. #6
    Join Date
    Oct 2004
    Location
    Canberra, Australia
    Posts
    13
    Will look into, thanks Healdem.
    Although I will probably rewrite the log file creation, Thanks StarTrekker.
    Either way I'm sure I'll have fun.
    Regards
    Greg

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Personally, I really don't think it matters who is logged in.
    I only ever store when a user logs in, part of this is because of things Healdem has already stated above but mainly because I've never found a real need to know who is currently around.

    If you can really justify a need to know who is logged in, then fine, but I can't see the point!
    George
    Home | Blog

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    The only reason I do it is to have a record of who is using the application and how long for... just for auditing reasons. I also don't use login logs to actually determine who is logged in at the time.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Oct 2004
    Location
    Canberra, Australia
    Posts
    13
    Thanks to all, just thought I'd post a quick reply to say I have solved it through using the same sql but writing to a single table, thus allowing a query with smart tags to then give me the user name (from outlook) based on the Windows logon userid. In answer to the other replies, I wanted to use it to know who to contact (small user base) before kicking them out (using a kick out based on timer routine) of the dbase to do maintenance. In researching this I stumbled across a kick-out based on a non - usage time out which would also serve this purpose as well. I did find the Microsoft info ( Thanks again Healdem) but that only works for access login based on security, which is no good in this instance where I am not using access security (i.e. all I got was admin multiple times) Thanks again to all the replies.

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Cool.

    My kickout feature doesn't care who it is that's logged in, it just tells whoever is logged in a custom message like "This application will close in 5 minutes for maintenance. It will be available again at 2:30pm" or whatever... and then gives them five minutes before quitting.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by StarTrekker
    Cool.

    My kickout feature doesn't care who it is that's logged in, it just tells whoever is logged in a custom message like "This application will close in 5 minutes for maintenance. It will be available again at 2:30pm" or whatever... and then gives them five minutes before quitting.
    nice one....
    might be worth adding to the code bank if you wish to.....

    I think you could enhance it by wiring all keyboards, tables & chairs to mains (has to be the meaty 220/240v AC, wussy US juice wouldn't suffice) and give the miscreants a belt for not shutting down immediately

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I think you could enhance it by wiring all keyboards, tables & chairs to mains (has to be the meaty 220/240v AC, wussy US juice wouldn't suffice) and give the miscreants a belt for not shutting down immediately
    lol

    Top idea!!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just a bit of follow up...

    I stumbled across this earlier today so I thought I'd post it. Afraid to say I have no idea whatsoever where it came from, or indeed, why I have it!

    Show logged in users
    Code:
    '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=db.mde"
    
        'cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
        '& "Data Source=db.mde"
    
        ' 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 & Chr(9) & rs.Fields(1).Name & Chr(9) & rs.Fields(2).Name & Chr(9) & rs.Fields(3).Name
    
        While Not rs.EOF
            Debug.Print RTrim(rs.Fields(0)) & Chr(9) & Chr(9) & RTrim(rs.Fields(1)) & Chr(9) & Chr(9) & RTrim(rs.Fields(2)) & Chr(9) & rs.Fields(3)
            rs.MoveNext
        Wend
    
        rs.Close
        cn.Close
        Set rs = Nothing
        Set cn = Nothing
    George
    Home | Blog

  14. #14
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Unfortunately, I believe this shows who has logged on since the LDB file was created. (In other words, the user name record in the LDB is never erased...)

    Only when the last user logs out, and the ldb file is erased, will the user record be erased.

    Ref Access - Who's on First???
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •