Check if File exists

[brharrii]

I have a table with several thousand records. Each record as a path field that indicates a specific file that is associated with a given record (not just a directory, it identifies a specific file). I'm pretty sure that most of them are correct but I've seen and been cleaning up paths that have had typos in them or were otherwise not setup correctly. Is there a way that I can generate a report or a list of some sort that would tell me exactly which file paths exist and which ones don't? or even just the ones that don't.

Thanks

Bruce

 

[MarkK]

Check out the VBA.FileSystem.Dir() function.

 

[ChrisO]

This sort of thing is not as easy as it may appear.

The question about if a file exists could very well be ambiguous. For example: Does a file exist for all users in a multi-user environment?
In other words; can they get at it?

In any case, the following code is based on a few assumptions:
There is a table called tblSomeTable.
That table contains a text field (255) called PathAndFile.
That table also contains a Boolean field called Exists.

The aim of the code is to change the field ‘Exists’ to True if PathAndFile can be found by the user, else False.

Sub TestIt()
    Dim strFileName As String

    With CurrentDb.OpenRecordset("Select * From tblSomeTable")
        Do Until .EOF
            [color=green]' This not only turns error display off it also sets Err.Number to 0[/color]
            On Error Resume Next
            
            strFileName = Mid(!PathAndFile, InStrRev(!PathAndFile, "\") + 1)
            
            .Edit
            !Exists = Dir(Nz(!PathAndFile, "\")) = strFileName And Err.Number = 0
            .Update
            
            .MoveNext
        Loop
    End With
    
End Sub

The above code requires no additional references.

Chris.

 

[Simon_MT]

An Alternative is Dir here is an example relying on another to prescribe the file:

Function GetPictureExist()

        If Dir(GetPicturePath) <> Empty Then
            GetPictureExist = -1
        Else
            GetPictureExist = 0
        End If
End Function

Simon

 

[brharrii]

Chris,

Your solution worked brilliantly, I found 4 files that were using bad file paths and was able to correct them. Thank you very much for the help.