LDB File

[danrobson]

All,

I want to run a compact on my backend database just after 5pm unattended.

If a user is linking to the database I want to compact I can't so get an error message. I've developed a method to send a message to the user asking them to log out, the problem I have is this:

If two users are logged in the LDB file shows both users currently using the database. When the first user logs out they are still shown in the ldb file. Therefore my compact process is still reporting them as using the database and sending them another message instead of seeing the second user and asking them to log out.

If another user access the database then user one is removed from the file and user 3 is added (is this making sense?).

How can I refresh the ldb file so users that log out are removed without the need for another user to access the file (unless that other user is me through code!)? I've tried using the OpenDatabase method however, no ldb file was produced.

If anyone understands what I'm trying to do pointers would be appreciated.

TIA

Dan

 

[FancyPrairie]

I got into this discussion here thread181-468123. It seems that what you are doing is part way there. You send a message to the user telling them to log off. Once this message is sent, have your program wait (say 2 minutes) and then set a log off flag. The users' machine, upon seeing the flag is set, will immediately issue an Application.Quit command. Any new users, attempting to log on, should see the flag is set and, thus, prevent the user from logging on. (You could also set the Open database exclusive flag).

 

[danrobson]

FancyPrairie,

Thanks for the pointer. I understand where the tread was going and have considered something along those lines.

My problem is the database I want to kick people out of is used by many different front end databases, but the worst people for locking the database when I need it are people who write their own stuff! They may not even be doing much just looking directly at the data in a table that they've linked from my database thus preventing me from compacting the database or deleting the table in preperation to recreate it.

 

[FancyPrairie]

Here's some code I use for Access 2000. Although I recommend using the workgroup file, any file should work. Had to strip some code out that was exclusive to my library so haven't tested it as is. But should work.

'+***************************************************************************************
'*
'*  Sub:        WhosLoggedOn
'*
'*  Author:     FancyPrairie
'*
'*  Date:       December, 2001
'*
'*  Function:   This routine will determine who is logged on the 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:  "\\server\ShareName\TheWorkgroup.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 "\\server\ShareName\TheWorkgroup.mdw"
'*              and sorted by "Computer_Name" in Ascending order.
'*
'*                  Dim rst As ADODB.Recordset
'*
'*                  Call WhosLoggedOn("\\server\ShareName\TheWorkgroup.mdw", rst)
'*
'+***************************************************************************************

Option Compare Database
Option Explicit

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 & &quot; &quot; & varAscDesc
    End If
    
'********************
'*  Exit Procedure  *
'********************
        
ExitProcedure:

    Set rs = Nothing
    Set cn = Nothing

    Exit Sub

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

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

End Sub

 

[danrobson]

I'm running 97 what's the chances of this working?