How to tell if an access lock file exists

[xenou]

I've created this function - does anyone know of any alternatives or see any improvements? The purpose would be to pass the db path to DBEngine(0) for a compact and repair, so I don't want to open the database, just find out if its in use. One thought that comes to mind is that perhaps the compact and repair would just return an error and I can trap that error?

(parameter f is the mdb file -- assume there is a module scope FileSystemObject available. So the purpose of this function is to see if there is an ldb or laccdb file of the same base name as the database in the same folder as the database)

[COLOR="Navy"]Private Function[/COLOR] LockFileExists(ByRef f [COLOR="Navy"]As[/COLOR] Object) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Boolean[/COLOR]
[COLOR="Navy"]Dim[/COLOR] s(1) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]

    [COLOR="SeaGreen"]'//Derive base name, without extension[/COLOR]
    s(0) = FSO.GetAbsolutePathName(f.Path)
    i = Len(FSO.GetExtensionName(f.Path))
    s(0) = Left(s(0), Len(s(0)) - i - 1)
    
    [COLOR="SeaGreen"]'//Check for lock files - .ldb/.laccdb[/COLOR]
    s(1) = s(0) & ".ldb"
    [COLOR="Navy"]If[/COLOR] FSO.FileExists(s(1)) [COLOR="Navy"]Then[/COLOR]
        LockFileExists = True
        [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Function[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    s(1) = s(0) & ".laccdb"
    [COLOR="Navy"]If[/COLOR] FSO.FileExists(s(1)) [COLOR="Navy"]Then[/COLOR]
        LockFileExists = True
        [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Function[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

ξ

 

[boblarson]

How about this (without the FSO):

Function LockFileExists() As Boolean
   Dim strLFileName As String
   Dim strCurDb As String
   Dim strCurDbPath As String
 
   strCurDbPath = CurrentProject.Path
   strCurDb = CurrentProject.Name
 
   Select Case Mid(CurrentProject.Name, InStrRev(strCurDb, ".") + 1)
 
   Case "mdb", "mde"
       strLFileName = strCurDbPath & "\" & Left(strCurDb, InStrRev(strCurDb, ".") - 1) & ".ldb"
   Case "accdb", "accde", "accdr"
       strLFileName = strCurDbPath & "\" & Left(strCurDb, InStrRev(strCurDb, ".") - 1) & ".lccdb"
 
   End Select
 
   If Dir(strLFileName) <> vbNullString Then
       LockFileExists = True
   Else
       LockFileExists = False
   End If
 
End Function

 

[xenou]

Hi Bob,

Thanks - this is a good alternative, and we are using a similar strategy of checking for the lock file file extensions.

I played with error trapping this afternoon and found that I could trap the error if compact and repair failed due to the DB being open. Probably I will use both - first check for the lock file based on file extension, and also wrap the compact/repair itself with inline error handling just in case of unexpected snafus (or if 5 years from now we have a new type of Access file).

Here's my test code (just to demonstrate the error trap). Note that in the process I found I already will hit the error if the DB is open, when I rename the file - so it's really even *before* I actually compact the DB that I'll find out if its in use or not.

[COLOR="Navy"]Sub[/COLOR] Foo()
[COLOR="Navy"]Dim[/COLOR] s(1) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] FSO [COLOR="Navy"]As[/COLOR] FileSystemObject
    
    [COLOR="Navy"]Set[/COLOR] FSO = CreateObject("Scripting.FileSystemObject")
    
    s(0) = "C:\myTemp\TestDB.mdb"
    s(1) = "C:\myTemp\TestDB_" & Format(Now, "yyyymmddhhnnss") & ".mdb"
    
    [COLOR="SeaGreen"]'//Rename DB file[/COLOR]
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
    FSO.MoveFile s(0), s(1)
        
    [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] Err [COLOR="Navy"]Then[/COLOR]
        
        [COLOR="SeaGreen"]'//Compact[/COLOR]
        DBEngine.CompactDatabase s(1), s(0)
        
        [COLOR="Navy"]If[/COLOR] Err [COLOR="Navy"]Then[/COLOR]
            [COLOR="SeaGreen"]'//Compact Failed: report error and restore filename[/COLOR]
            FSO.MoveFile s(1), s(0)
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="SeaGreen"]'//Compact Succeeded: cleanup temp file (or it could be saved as a backup)[/COLOR]
            FSO.DeleteFile s(1)
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="SeaGreen"]'//File could not be re-named.[/COLOR]
    
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

 

[Kreszch68]

Hi Xenou,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Testing for a lock file isn’t the most reliable way to test if a DB is NOT open, only to test if a DB is OPEN. Meaning that a lock file can exist even if the database is closed, this can occur after a crash for example.<o></o>
<o> </o>
So what you really want is to check if the mdb file is open.<o></o>
Best and fastest way to do this is on a binary level.<o></o>
<o> </o>
The next function will return true if the file is open (can be used for all file types)<o></o>

Function FileIsOpen(sFile As String) As Boolean
 
Dim hFile As Long
On Error GoTo OpenError
    hFile = FreeFile
Open sFile For Input Lock Read As #hFile
Close #hFile
Exit Function

OpenError:
'If err number = 70 the file is open
FileIsOpen = Err.Number = 70
 
End Function

 

[xenou]

Thanks. That's a great tip. I'm thinking it may be useful to check if the lock file exists or not, anyway --> my intention is to a compact and repair, and perhaps it's a bad idea to compact and repair a database that has crashed. Any thoughts?

 

[boblarson]

One thing about the locking file. You can attempt to delete it and if it fails (throws an error) that should let you know that the program is open (99% of the time). There have been situations (my experience was with a Novell network) where the program is not open, the ldb file exists, but it cannot be deleted. In my case (it happened back between 2000 and 2002) the Novell Network Administrator had to use a special kill command to get rid of the file. But other than that possible scenario, it would be reliable.