Network file path not opening in VBA/MSAccess

Question

I'm using VBA in MS Access, and one of the subs takes a file path in a network, checks if the file exists or not, and write the result of a query on it. The problem is that when I try to run the code, it gives me error 52 (Bad file name or number). But if I open the network path in windows explorer first, for example, after that the error doesn't happen anymore. Any ideas on what the problem might be?

Here is some of the code I'm running:

fpath = "\\networkpath\file.txt"
DeleteFile fpath

Sub DeleteFile(ByVal FileToDelete As String)
    FileExists(FileToDelete) Then
        SetAttr FileToDelete, vbNormal
        FileToDelete
    End If
End Sub

Function FileExists(ByVal FileToTest As String) As Boolean
    FileExists = (Dir(FileToTest) <> "") 'this is where the error happens
End Function

If it prompts error while running it without manually accessing the folder first, chances are there is permission issue accessing the folder — Alex
– Alex, Commented Dec 17, 2014 at 18:23
See if you can run with this: allenbrowne.com/func-11.html .. Play around with the file path as well. — Mark C.
– Mark C., Commented Dec 17, 2014 at 18:24
Is this a WebDAV path ? I've seen this behavior with that type of path, but don't have a clean solution. You could try opening Explorer at that path via VBA before checking to see if the file exists. — Tim Williams
– Tim Williams, Commented Dec 17, 2014 at 18:31
Had no luck using the code in that link, so I had to resort to opening explorer, like Tim suggested, which works — htcoelho
– htcoelho, Commented Dec 17, 2014 at 19:20

Renaud Bompuis · Accepted Answer · 2014-12-18 06:51:56Z

2

Does the UNC path you use contain any non-Ascii characters, like accents? What is the exact path?

None of the file functions in VBA work well with Unicode anyway.

You could try to use the FileSystemObject to achieve the same a bit more reliably than the build-in VBA functions:

Public Function FileExists(filePath as string) as Boolean
    Dim o As Object
    Set o = CreateObject("Scripting.FileSystemObject") 
    FileExists = o.FileExists(filePath) 
End Function

An alternative using the Win32 API tha works in 32 and 64 bit environments:

Private Const INVALID_FILE_ATTRIBUTES As Long = -1

#If VBA7 Then ' Win API Declarations for 32 and 64 bit versions of Office 2010 and later
  Private Declare PtrSafe Function GetFileAttributes Lib "kernel32" Alias "GetFileAttributesW" (ByVal lpFileName As LongPtr) As Long
#Else ' WIN API Declarations for Office 2007
  Private Declare Function GetFileAttributes Lib "kernel32" Alias "GetFileAttributesW" (ByVal lpFileName As Long) As Long
#End If

Public Function FileExists(fname As Variant) As Boolean
    If IsNull(fname) Or IsEmpty(fname) Then Exit Function
    ' Make sure that we can take care of paths longer than 260 characters
    If Left$(fname, 2) = "\\" Then
        FileExists = GetFileAttributes(StrPtr("\\?\UNC" & Mid$(fname, 2))) <> INVALID_FILE_ATTRIBUTES
    Else
        FileExists = GetFileAttributes(StrPtr("\\?\" & fname)) <> INVALID_FILE_ATTRIBUTES
    End If
End Function

edited Dec 18, 2014 at 6:51

answered Dec 18, 2014 at 6:41

Renaud Bompuis

16.8k4 gold badges59 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

htcoelho Over a year ago

sadly, it doesn't work. the problem, apparently, is something with permissions. +1 though for the tip and code for file reading

Collectives™ on Stack Overflow

Network file path not opening in VBA/MSAccess

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related