Access User List

[wandan]

Hi There,

Is there a way to find out who has an Access database open? A co-worker is trying to update her database and it keeps telling her that someone has it open but she can't tell who it is.

Thanks.

 

[sfreeman]

Try this:

http://www.mvps.org/access/modules/mdl0055.htm

Sam_F
"90% of the problem is asking the right question.

 

[VBslammer]

I normally populate a Listbox (with 4 columns) using semi-colon delimited value list retrieved by a function (uses the project's active connection):

Function GetCurrentUsers() As String
  Dim cnn As ADODB.Connection
  Dim rst As ADODB.Recordset
  Dim strReturn As String
  
  Set cnn = CurrentProject.Connection

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

  With rst
    While Not .EOF
      strReturn = strReturn & _
        NullTrim(.Fields("COMPUTER_NAME")) & ";" & _
        NullTrim(.Fields("LOGIN_NAME")) & ";" & _
        NullTrim(.Fields("CONNECTED")) & ";" & _
        NullTrim(.Fields("SUSPECT_STATE")) & ";"
      .MoveNext
    Wend
  End With
  
  GetCurrentUsers = strReturn

End Function

Function NullTrim(ByVal vValue As Variant) As String
  Dim lngIndex As Long
  
  vValue = Trim(Nz(vValue, "(Null)"))
  lngIndex = InStr(vValue, Chr(0))
  
  If lngIndex > 0 Then
    NullTrim = Left(vValue, lngIndex - 1)
  Else
    NullTrim = vValue
  End If
End Function

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[VBslammer]

To use the example above, put a listbox on a form, about 5" wide, and add this code to the Form_Load() event of the form:

Private Sub Form_Load()
  With Me!lstUsers
    .ColumnCount = 4
    [green]'settings for a 5" wide listbox:[/green]
    .ColumnWidths = "1.5"";1.5"";1"";1"""
    [green]'turn on column headings:[/green]
    .ColumnHeads = True
    .RowSource = "ComputerName;Login;Connected?;Suspect?;" & GetCurrentUsers()
    [green]'select the first row:[/green]
    If .ListCount > 1 Then
      .Selected(1) = True
    End If
  End With
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[VBslammer]

Forgot the RowSourceType property:

Private Sub Form_Load()
  With Me!lstUsers
    .ColumnCount = 4
    [blue].RowSourceType = "Value List"[/blue]
    [green]'settings for a 5" wide listbox:[/green]
    .ColumnWidths = "1.5"";1.5"";1"";1"""
    [green]'turn on column headings:[/green]
    .ColumnHeads = True
    .RowSource = "ComputerName;Login;Connected?;Suspect?;" & GetCurrentUsers()
    [green]'select the first row:[/green]
    If .ListCount > 1 Then
      .Selected(1) = True
    End If
  End With
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[sfreeman]

VB....

Should this form display only 'other' users in the db, or will it also show myself in the db?

Sam_F
"90% of the problem is asking the right question.

 

[sfreeman]

Cancel my last e-mail....

thanks, pretty cool :-B

Sam_F
"90% of the problem is asking the right question.

 

[wandan]

Hi There,

This looks really great and I have been trying to make it work and am having some issues. We just upgraded from 97 (DAO) to 2003 (ADO) and so most of my code is in DAO.

I copied in your code into my coworkers machine and it doesn't get past the first line of GetCurrentUsers. Tried it on my PC and it dies on OpenSchema.

Any suggestions of how/what I need to do to get this code to work?

Thanks!

 

[VBslammer]

Not sure - I don't have Access 2003. I tested it on Access XP (2002) and it works OK. Do you have a reference to the "Microsoft DAO 3.6 Object Libary" in your project? Things never work properly if a reference is missing.

VBSlammer

"You just have to know which screws to turn." - Professor Bob