Read MS Access lock file in Excel VBA

Email
Home

Below is a piece of code to read and display the contents of an MS Access lock file. Useful when you are waiting for the backend database to come free so that it can be worked on. The contents of a lock file are only the device names of the computers but it is useful if you can decode the device name and match it to a user.

Sub sbRead_laccdb()
    Columns("A:A").Select
    Selection.ClearContents
    Range("A1").Select
    Dim FileNum As Integer
    Dim DataLine As String
    FileNum = FreeFile()
    Open "Z:\Performance\Database\Area 51\Backend\sddb_backend.laccdb" For Input As #FileNum
    While Not EOF(FileNum)
        Line Input #FileNum, DataLine
    Wend
    Dim i As Long
    Dim sDevice As String
    With ActiveSheet
    For i = 1 To 20
        sDevice = Mid(DataLine, 1, 62)
        .Cells(i, 1).Value = Mid(sDevice, 1, InStr(1, sDevice, " ") - 1)
        DataLine = Mid(DataLine, 63, Len(DataLine))
        If Len(DataLine) = 0 Then Exit Sub
    Next i
    End With
End Sub

Tags - Excel 2010, Excel 2007, lockfile, VBA, code, Microsoft, Visual BASIC for Applications, device, names, MS Access 2007, laccdb, backend ...