Database question

[mrwendell]

I need assistance with...

1. how to timeout a user from my database when they have been inactive for say 20 minutes?

2. how can i see who is logged into my database?...users donot have to log into a wrkgrpadmin... management does because they are members of a wrkgrp that is used in another database...so the permission migrate

can I do either/both in code or property settings?

 

[RickSpr]

1. Create a global (module-level) variable called, say, datLastActivity. At key points in your application (enough so that any user activity will definitely send you through at least one of them), set the variable to Now().

Create a blank form and set its TimerInterval to, say, 60000=1 minute. In the Timer event code, get the value of the global variable and DateAdd 20 minutes to it. If the result is less than Now(), their 20 minutes are up; quit the application. Arrange for this form to be opened invisibly when the database is opened.

This is a pretty brute-force method. You may want to make it a little more sophisticated by checking for things like edits in progress so you can Undo them first.

2. Code to obtain a list of logged-on users is available in the Microsoft Knowledge Base article 198756. It also shows you how to quiesce the database so new users can't log on while you're allowing existing users to finish up.

FancyPrairie recently (within the last 2 weeks) described a pretty comprehensive technique which combined on-demand user shutdown, current user monitoring, and other administration needs. You may want to search for that post.

There are also commercial products such as Access Workbench which attempt to provide some centralized administration tools, if you're interested.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[LittleSmudge]

1) Dim a global variable As Date/Time and every time the use "Does something" set the variable = Now()

Then have a hidden form that opens when the database is launched ( By the Startup form )

Set the Timer on this hidden form to 60000 ( 1 minute )

In the On Timer event check
If Now() - GlobalVar > ( 1 / 72 ) Then 1/72 = 20mins
Application.Quit
End If



2) Permissions DO NOT 'migrate' between databases just because you use the same security.MDW file.

The information stored in the Security.MDW file is limited to :-
UserNames and Groups and which Users belong to which Groups
and who is actually using the Security.MDW file at that moment in time.

The Permissions for any particular database are stored in the Database itself.

However, you can use the following code to display the results of a Call to WhosLoggedOn()

Private Sub Form_Load()
Dim rstLI As ADODB.Recordset
Set rstLI = New ADODB.Recordset
rstLI.ActiveConnection = CurrentProject.Connection
rstLI.CursorType = adOpenKeyset
rstLI.LockType = adLockOptimistic

Call WhosLoggedOn("\\172.16.1.251\ssd\Security.mdw", rstLI)
txtResults = ""
While Not rstLI.EOF
    txtResults = txtResults & Left(rstLI.Fields(0), Len(Trim(rstLI.Fields(0))) - 1) & "    " _
                            & Left(rstLI.Fields(1), Len(Trim(rstLI.Fields(1))) - 1) & "    " _
                            & rstLI.Fields(2) & "    "
    If Nz(rstLI.Fields(3), 0) = 0 Then
        txtResults = txtResults & "Okay" & vbCrLf
    Else
        txtResults = txtResults & "Suspect" & vbCrLf
    End If
    rstLI.MoveNext
Wend
End Sub

WhosLoggedOn is an excellent piece of well documented code posted by its author on these boards some time ago.
As SEARCH is not too well at the moment I take the liberty of reposting his code below.

'+************************************************************************'*
'*  Sub:        WhosLoggedOn
'*  Author:     FancyPrairie
'*  Date:       December, 2000
'*  Function:   This routine will determine who is logged on the database specified
'*              by the caller (generally it should be the Workgroup database).
'*              This routine will return the following info in the Recordset passed by
'*              the Caller:
'*
'*              rst.Fields(0).Name = "Computer_Name"    (Char:    Name of the computer)
'*              rst.Fields(1).Name = "LOGIN_NAME"       (Char:    Name of user whos logged in)
'*              rst.Fields(2).Name = "CONNECTED"        (Boolean: True if Connected)
'*              rst.Fields(3).Name = "SUSPECT_STATE"    (Integer: Null if not suspect)
'*
'*  Arguments:  strWorkgroup (string)
'*              ---------------------
'*              This string contains the path (and name) of the database you want to
'*              see who's logged in. Usually you would check the workgroup file.
'*              (Example:  "\\path\YourWorkgroup.mdw"
'*
'*              rst (ADODB.Recordset)
'*              ---------------------
'*              This recordset will be returned to the caller.  It will contain the
'*              names of the computers that are logged on to "strWorkgroup" (see the
'*              description of the recordset above).
'*
'*              NOTE:  This routine will create the recordset and populate it.
'*
'*              varSortField (variant - Optional)
'*              ---------------------------------
'*              This variable indicates which field you want the recordset sorted by.
'*              If this argument is not passed, the recordset will not be sorted.  The
'*              possible values for this variable are:
'*                 -1 = Don't sort the data
'*                  0 = Sort by rst.Fields(0)   (Computer_Name)  (DEFAULT)
'*                  1 = Sort by rst.Fields(1)   (Login_Name)
'*                  2 = Sort by rst.Fields(2)   (Connected)
'*                  3 = Sort by rst.Fields(3)   (Suspect)
'*
'*              varAscDesc (variant - Optional)
'*              -------------------------------
'*              Indicates how the data is to be sorted.  The 2 possible values are:
'*                  "ASC"  = Sort Ascending (DEFAULT)
'*                  "DESC" = Sort Descending
'*
'*  Example:    The following is an example of how to call this routine.  The call shown
'*              will return all of the computers logged on to "\\path\YourWorkgroup.mdw"
'*              and sorted by "Computer_Name" in Ascending order.
'*
'*                  Dim rst As ADODB.Recordset
'*
'*                  Call WhosLoggedOn("\\path\YourWorkgroup.mdw", rst)
'*
'+*************************************************************************

Public Sub WhosLoggedOn(strWorkgroup As String, _
                            rst As Recordset, _
                   Optional varSortField As Variant = 0, _
                   Optional varAscDesc As Variant = "Asc")

'********************************
'*  Declaration Specifications  *
'********************************

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    
    On Error GoTo ErrHandler

'*************************
'*  Open Workgroup file  *
'*************************

    cn.Provider = "Microsoft.Jet.OLEDB.4.0"
    cn.Open "Data Source=" & strWorkgroup

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

'*********************************
'*  Create Fields for Recordset  *
'*********************************

    Set rst = New ADODB.Recordset

    rst.Fields.Append rs.Fields(0).Name, adVarWChar, 32
    rst.Fields.Append rs.Fields(1).Name, adVarWChar, 32
    rst.Fields.Append rs.Fields(2).Name, adBoolean
    rst.Fields.Append rs.Fields(3).Name, adInteger
    
'*************************************************************************
'*  Loop thru Recordset and add Computer Name, etc. to user's recordset  *
'*************************************************************************

    rst.Open
    
    While Not rs.EOF
        
        rst.AddNew
        If (Not IsNull(rs.Fields(0))) Then rst.Fields(0) = rs.Fields(0)
        If (Not IsNull(rs.Fields(1))) Then rst.Fields(1) = rs.Fields(1)
        If (Not IsNull(rs.Fields(2))) Then rst.Fields(2) = rs.Fields(2)
        If (Not IsNull(rs.Fields(3))) Then rst.Fields(3) = rs.Fields(3)
        rst.Update
        
        rs.MoveNext
    Wend

    If (varSortField <> -1) Then
        rst.Sort = rst.Fields(varSortField).Name & " " & varAscDesc
    End If
    
'********************
'*  Exit Procedure  *
'********************
        
ExitProcedure:

    Set rs = Nothing
    Set cn = Nothing

    Exit Sub

'****************************
'*  Error Recovery Section  *
'****************************
        
ErrHandler:

    Err.Raise vbObjectError + 20100, "Error occcurred in function WhosLoggedOn", "Error Number: " & Err.Number & vbCrLf & vbCrLf & "Error Description: " & Err.Description
        
    Resume ExitProcedure

End Sub

Store WhosLoggedOn in a Global module and call it as above from a Form.




'ope-that-'elps.


G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.