Commonly, to check for the existence of a file we advise people to use the Dir function or FileSystemObject. The problem that can be encountered with these approaches is that, for network drive & mapped drives, should they be disconnected, can result in long delay because you have to wait to the function to timeout.

I set out to try and resolve this issue and this is what I came up with. As it turns out, it was slightly more involved than I originally thought and as such, I broke certain elements into independent function should they prove useful for other application.

Function FileExist(ByVal sFile As String) As Boolean
    On Error GoTo Err_Handler

    If IsPathAccessible(sFile) = True Then
        If Len(Dir(sFile)) > 0 Then
            FileExist = True
        End If
    End If

Exit_Err_Handler:
    Exit Function

Err_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: FileExist" & vbCrLf & _
           "Error Description: " & Err.Description, vbCritical, "An Error has Occurred!"
    GoTo Exit_Err_Handler
End Function

Function IsPathAccessible(ByVal sPath As String) As Boolean
    On Error GoTo Error_Handler
    'Inspired by: https://www.experts-exchange.com/questions/28165714/Ping-in-VBA.html
    Dim oPing                 As Object
    Dim oRetStatus            As Object
    Dim sDriveLetter          As String

    'UNC path checks
    If Left(sPath, 2) = "\\" Then
        sPath = Split(Mid(sPath, 3), "\")(0)
        GoTo CheckPath
    End If

    'Drive Letter checks
    If sPath Like "[A-Z]:\*" Then
        'Extract just the driveletter
        sDriveLetter = Split(Mid(sPath, 1, 1), "\")(0)
        'Check if it is a Local or Network drive
        If IsNetworkDrive(sDriveLetter) = True Then
            'Network drive
            sPath = GetUNC(sDriveLetter)
            If Left(sPath, 2) = "\\" Then
                sPath = Split(Mid(sPath, 3), "\")(0)
            End If
            GoTo CheckPath
        Else
            'Local drive so nothing to worry about
            IsPathAccessible = True
            GoTo Error_Handler_Exit
        End If
    End If

CheckPath:
    Set oPing = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery _
                ("select * from Win32_PingStatus where address = '" & sPath & "'")
    For Each oRetStatus In oPing
        If IsNull(oRetStatus.StatusCode) Or oRetStatus.StatusCode <> 0 Then
            IsPathAccessible = False
        Else
            IsPathAccessible = True
            Exit For
        End If
    Next

Error_Handler_Exit:
    On Error Resume Next
    If Not oRetStatus Is Nothing Then Set oRetStatus = Nothing
    If Not oPing Is Nothing Then Set oPing = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: IsPathAccessible" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Public Function IsNetworkDrive(sDrive As String) As Boolean
    On Error GoTo Error_Handler
    Dim oDrive                As Object

    For Each oDrive In CreateObject("Scripting.FileSystemObject").Drives
        If oDrive.DriveLetter = sDrive Then
            If oDrive.DriveType = 3 Then IsNetworkDrive = True
        End If
    Next

Error_Handler_Exit:
    On Error Resume Next
    If Not oDrive Is Nothing Then Set oDrive = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: IsNetworkDrive" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Function GetUNC(sMappedDriveLetter As String) As String
    On Error GoTo Error_Handler

    'Convert mapped drive letter to UNC path
    GetUNC = CreateObject("Scripting.FileSystemObject").Drives(CStr(sMappedDriveLetter)).ShareName

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: GetUNC" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

How Does It Work Exactly

I know there is a fair bit of code, but it is actually very straight forward.

First it examines the path to determine if it is a UNC format or not. If it isn’t, then it checks to see if the Drive is a Mapped drive and extracts the UNC path. If it is a local drive then it simply proceeds with the check. If on the other hand it is a UNC path, it first attempts to Ping the server. If it can’t then that’s it, if it can then it proceeds with the check of the file.

The key to the performance improvement come from the ping prior to testing for the file as the ping is much quicker at returning a response vs. the DIR function timeout.

The Results

Now based on a few tests, these were the results I got.

For Local Files

Both techniques performed similarly, which is to say instantaneously

For Mapped Drive Paths

When Connected

The above outperformed the standard Dir approach by 95%

When Disconnected

The above outperformed the standard Dir approach by 74%
For subsequent calls however the DIR function sometimes outperformed this approach by 43%.

It would appear that the DIR function has some form of caching, but the caching was inconsistent. Sometimes the method above was much faster and then all of a sudden the caching would kick in an DIR would outperform my method. This is why I’m saying DIR was faster for subsequent calls, but that isn’t always the case.

For UNC Network Paths

When Connected

Both approaches were similar in performance with only a negligible improvement with the above approach.

When Disconnected

The above outperformed the standard Dir approach by 83%
There was no change in performance for subsequent calls and the above continued to outperform the DIR function by 83% typically.

Conclusions

If your database is a local database for personal use and your links are to files on your computer then DIR makes sense as it works very efficiently and is simpler to implement.

On the other hand, if your links are to network locations, www files and you are following the best practice of using UNC paths, the above approach seems to offer a potential performance improvement over the commonly promoted techniques.

Of course, the choice is yours, but I had fun playing around with all of this and trying to come up with a more efficient technique and was surprised by the potential improvements that can be achieved over the DIR(). Hopefully this new approach can help a few people.