MSAccess crashes opening a file

Question

I am using Office 365 on a windows 10 PC.

I have some VBA code in Access to check whether a file is open or locked (the file is local to this PC).

On one computer this code runs for most files, but consistently crashes when it reaches a particular set of files. It is the same set of files each time if I manually step through the code to move on from the first file. I tried rebooting the PC to clear any locks, but the result is the same.

When I say crash, I mean that I loose control of Access and windows reports that it is no longer responding.

If I run the same code on a different PC, referring to the same files, it reports the file is locked, but does not crash.

The file is not locked, or not in the way I understand file locking. From the user interface, I can rename, move or delete the files at will.

I am fairly certain there is nothing wrong with the VBA code as written and am beginning to think there may be a corrupt DLL somewhere.

VBA references

My code crashes at the line Open my_source For Input Lock Read As #ff

Function copyormovemyfiles(my_source As String, my_dest As String, mycontrol As Integer) As Boolean
Dim fso As Scripting.FileSystemObject
Dim ff As Long, ErrNo As Long
''''''''''''''''

' mycontrol = 1 for a move
' mycontrol = 2 for a copy.   It will not overwrite files

''''''''''''''''

On Error GoTo error_control

Set fso = New Scripting.FileSystemObject
If Not fso.FileExists(my_source) Then
    Err.Raise 1000, , my_source & " does not exist!" & vbExclamation & "Source File Missing"

ElseIf Not fso.FileExists(my_dest) Then        
    fso.CopyFile my_source, my_dest, True

Else
    Err.Raise 1000, my_dest & " already exists!" & vbExclamation
End If


Select Case mycontrol

Case 1
    On Error Resume Next
    ff = FreeFile()
    Open my_source For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    'On Error GoTo 0
    If ErrNo > 0 Then Stop
    Err.Clear
    'Select Case ErrNo
    'Case 0:    IsWorkBookOpen = False
    'Case 70:   IsWorkBookOpen = True
    'Case Else: Error ErrNo
    'End Select

    On Error GoTo error_control

What happens if you remove `On Error Resume Next`? Access going "not responding" might just be a consequence of trying/failing to acquire the file lock... also I doubt `Err.Cl` is compilable. — Mathieu Guindon, Nov 05 '19 at 17:48
Thank you Mathieu. The err.Cl is simply a typo. The code actually says err.Clear. — Doug, Nov 06 '19 at 10:44
Mathieu. If I remove the on error resume next, the code still crashes in exactly the same manner. The only difference is that on the second PC, the so called file lock does get trapped and (correctly) jumps to the error handling code. So good challenge, but does not change anything. — Doug, Nov 06 '19 at 10:49
A full [Decompile](https://stackoverflow.com/a/3268188/3820271) might help. — Andre, Nov 06 '19 at 12:05
Andre - I have just done that and it has made no difference. Thanks for making the suggestion. — Doug, Nov 06 '19 at 19:05

HackSlash · Answer 1 · 2019-11-06T22:45:02.930

1

It's best to just do an action and then deal with the fail case instead of testing beforehand. The reason is that the state could change between your test and the action. Also, you are raising errors manually when you can just let your code raise it's errors organically.

So you say your copy won't overwrite but then you tell the copy command to overwrite. If we tell it not to overwrite then we no longer have to test if the source or destination exist, they both result in clear errors.

NOTE: Don't use underscore "_" in variable or function names because those are used for event definitions in the VBA event handler.

Function copyormovemyfiles(my_source As String, my_dest As String, mycontrol As Integer) As Boolean
    ''''''''''''''''
    ' mycontrol = 1 for a move
    ' mycontrol = 2 for a copy.   It will not overwrite files
    ''''''''''''''''
    On Error GoTo error_control
    Dim fso As Scripting.FileSystemObject    
    fso.CopyFile my_source, my_dest, overwrite:=False

    If mycontrol = 1 Then 
        SetAttr my_source, vbNormal
        fso.DeleteFile my_source
    End If

    copyormovemyfiles = True

error_control:
    If Err.Number <> 0 Then
        ' You can select case here and handle the error
        copyormovemyfiles = False
    End If
End Function

edited Nov 06 '19 at 22:45

answered Nov 05 '19 at 23:18

HackSlash

4,944
2
18
44

Thank you Hackslash. I have tried changing the code around many times over the last few weeks as I looked for a solution. In fact, your suggestion is where I started. The same fault occurs if I try to use a move, deletefile or kill command on the file (and I am sure there will be others). In terms of your question "you are raising errors manually when you can just let your code raise it's errors organically", this is just the latest iteration where I am trying to catch the error before it crashes, although actually it is too late by then. – Doug Nov 06 '19 at 21:05
Hackslash. I reached the character limit on previous comment. Here is the answer to the remainder of your questions. you commented about underscores. I can confirm that removing them makes no difference to the execution or the crash.. Hope this all helps. – Doug Nov 06 '19 at 21:06
The only time I ever encountered a problem like this was something to do with attributes. I found some code laying around where I am manually setting attributes before I delete. Maybe this will fix the problem or give you a better error to work with. I added `SetAttr my_source, vbNormal` to the code example – HackSlash Nov 06 '19 at 22:46
Thank you Hackslash. Apologies for the delay replying. I made your suggested modification, which unfortunately made no difference. – Doug Nov 09 '19 at 16:24
Hackslash. I should add that the errror reported when I run the code on the second PC is now 70, permission denied. – Doug Nov 09 '19 at 16:27
Interestingly, and this is the first time I have spotted this, the files where it fails are different between the two PCs. – Doug Nov 09 '19 at 16:30
Permission denied is a very clear error. Either the user doesn't have permission or the file is open. If the file is open there is nothing you can do to delete it. You have to wait for the lock to clear. If Access no longer crashes then your bug is fixed. – HackSlash Nov 11 '19 at 16:18
Thanks HackSlash. If only it were that simple. The file is not open, I can delete it from a manual command (e.g. using file explorer). And why only selected files? This routine is running against 50 files, so why just fail with this error on three or four. – Doug Nov 11 '19 at 18:36
Here is the official MS article on the subject: https://support.microsoft.com/en-us/help/2623670/access-denied-or-other-errors-when-you-access-or-work-with-files-and-f – HackSlash Nov 11 '19 at 19:02
Thanks for everything HackSlash, but this is only when trying to access files from the user interface. An none of these cause any errors. I am going to stop here and conclude this is either a bug in MS windows 10 or there is a corrupt DLL on my PC. – Doug Nov 11 '19 at 22:49
I've seen "Preview Pane" in Explorer lock files as open. Checking for corruption in the OS is as simple as `SFC /scannow` – HackSlash Nov 11 '19 at 22:54

MSAccess crashes opening a file

1 Answers1