CODEWITHSUNDEEP
vbams-accessms-access-2013
htcoelho
htcoelho

Reputation: 65

Network file path not opening in VBA/MSAccess

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

Upvotes: 2

Views: 1558

Answers (1)

Renaud Bompuis
Renaud Bompuis

Reputation: 16786

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

Upvotes: 2

Related Questions