Testing for exclusive access

[slynn]

I have a database thats is used by about 20 people. I want the first person in that database to run a series of Macros. Is there an easy way to tell if they are the only one in the database?

Thanks.

[NeoPa]

I have a database which logs the Date/Time of running my once-a-day code in a single record table.

Before logging anything it compares the logged date with the current Date(). If the dates are different (not the times - just the date portions), then it goes on to check another flag in the same record which indicates whether someone already has the database open and is running the once-a-day code.

If this flag is set to True, then a message pops up telling the operator that the database is currently unavailable due to once-a-day processing.

If set to False, then the flag is set to True and the once-a-day code is executed. When it is completed, the flag is reset to False and the Date/Time is logged.

[ADezii]

Is there an easy way to tell if they are the only one in the database?

We can do one better that that. The following code will tell how many Users are logged on to a Database, and also display their Names in the form of a Message Box. The code is extremely flexible and can be used in any manner you so desire: in a Function, Click() Event of a Command Button, Open() Event of a Form, etc. Just Copy and Paste:

Dim cnn As ADODB.Connection
Dim strUser As String
Dim rst As ADODB.Recordset
Dim intUser As Integer
Dim varValue As Variant
Const conUsers = "{947bb102-5d43-11d1-bdbf-00c04fb92675}"
  
Set cnn = CurrentProject.Connection
Set rst = cnn.OpenSchema(Schema:=adSchemaProviderSpecific, SchemaID:=conUsers)
  
With rst
  Do Until .EOF
    intUser = intUser + 1
      varValue = .Fields(1).Value
      If InStr(varValue, vbNullChar) > 0 Then
        varValue = Left(varValue, InStr(varValue, vbNullChar) - 1)
      End If
      strUser = strUser & varValue & vbCrLf
      .MoveNext
  Loop
End With

strUser = Left$(strUser, Len(strUser) - 2)
 
MsgBox "There are currently " & intUser & " User(s) logged on to this " & _
       "Database, and they are:" & vbCrLf & vbCrLf & strUser, _
       vbInformation, "Logged On Users"
  
'Routine cleanup chores
Set fld = Nothing
Set rst = Nothing
Set cnn = Nothing

P.S. - This code is actually a subset of more comprehensive code relating to Generating a User List ==> How to Generate a User List

[slynn]

We can do one better that that. The following code will tell how many Users are logged on to a Database, and also display their Names in the form of a Message Box. The code is extremely flexible and can be used in any manner you so desire: in a Function, Click() Event of a Command Button, Open() Event of a Form, etc. Just Copy and Paste:

Works perfectly. Thanks!

[ADezii]

Works perfectly. Thanks!

You are quite welcome.