Detect if File Open

[Tezcatlipoca]

Hi All,

I'm not sure if this can be done or not, but I have a database project that includes - via VB - some file manipulation in the background.

This all works perfectly, but there is a problem if the file to be manipulated is currently open by a user other than the one trying to make the change in the database.

When you open up a shared file, such as a Word document, in a network you are informed if another user has it open and your copy opens in Read Only.

Is it possible to replicate that with VBA inside Access? So, for example, I have a button on my database that allows authorised users to delete a file that exists in a shared folder. Is it possible to introduce code that checks if that file is already being accessed, then returns some value to Access so I cna build the appropriate error message into the database?

 

[datAdrenaline]

What type of files are you working with? If you are working with Excel, Word, PDF's and such, you can use the VBA methods:

FileCopy()
Kill()

And if you try to manipulate a file opened by Word, Excel, Adobe, etc. you will get a trappable run-time error (I beleive it has an error number of 70).

 

[Tezcatlipoca]

Hi there, and thanks for the heads up. Yes, these are all Office files (so Word, Excel or Powerpoint).

I'll try to investigate these methods a little more, but do you have any examples of them in working code I could look at? I'd like to get a handle for how these can be fully manipulated.

 

[The_Doc_Man]

Usually, the simplest way to do that is to declare a trap handler around the code in question. This is a very non-specific handler that tests for error 70. You would need to verify that in fact it IS error 70 that is signaled when you have the problem, but this is about how you might do it.

    FileBusyFlag = FALSE
    On Error GoTo FileTrap
    Kill TheFile
    On Error GoTo 0
    GoTo NextThingToDo

FileTrap:
    If Err.Number = 70 Then
        FileBusyFlag = TRUE
    Else
        {report an unexpected trap}
    End If
    Resume NextThingToDo

NextThingToDo:
    If FileBusyFlag = TRUE Then
        {if required, do a MsgBox function to report the condition}
        GoTo FileWasBusy
    End If

 

[Tezcatlipoca]

Thanks guys, I have it working well now. In case anyone else is trying to do the same thing, here's my final code:

Private Sub btnWipe_Click()
    FileBusyFlag = False
    On Error GoTo FileTrap
    Kill Me.FilePath
    MsgBox "File Wiped"
    On Error GoTo 0
    GoTo NextThingToDo
FileTrap:
    If Err.Number = 70 Then
        FileBusyFlag = True
    Else
        MsgBox "Unexpected Error"
    End If
    Resume NextThingToDo
NextThingToDo:
    If FileBusyFlag = True Then
        MsgBox "File is Open"
    End If
End Sub

The 'Me.FilePath' variable is an unbound text box that simply gets updated with the full path and filename each time the user selects a file from a list.

 

[gemma-the-husky]

i think this could be simplified. this probably does the same job.
I think you might use a function instead of a sub, and return a value that indicates success/failure. Depends whether you want to use this to delete a number of files together.

Private Sub btnWipe_Click()

    On Error GoTo Fail
    Kill Me.FilePath
    MsgBox "File Wiped"
    exit sub

fail:
    If Err.Number = 70 Then
        msgbox "File is Open"
    Else
        MsgBox "Unexpected Error" & vbcrlf & "Err: " & err.number & " Desc: " & err.description
    End If

End Sub

 

[Tezcatlipoca]

Ah, thanks Gemma; that also works wonderfully. I've actually changed my code to yours since, despite both working, yours is a little cleaner.