MDE files and access

[M8KWR]

I have mde files that are held on a server for each member of staff to access.

Is there anyway to find out which user is in the file, as there could be upto 6 at any one time, and trying to find the person to exit the file so i can update it is becoming more of an issue.

many thanks for your time.

 

[LittleSmudge]

When a user open the mde Access creates a .ldb file.

You can write an app that interogates the .ldb file to find out who is in it

The following code may help which I copy here with acknowledgement to the original author FancyPrairie from another thread on this site ( cause it's easier than finding it )

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("\\server\path\System.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


'+************************************************************************'*
'*  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

- or you can open the .ldb file ( using NOTEPAD ) and read who is in the db.


'ope-that-'elps.


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

 

[M8KWR]

many thanks for this.