File Location and File Path

[itsalesguru]

I need help,
have 17,000 files cross 9 different directories, have an access database with a column with files's path. How can I make sure that the path for individual files are correct and files exist in directories that databse indicates they are.

 

[apr pillai]

A small VBA Routine is given below:

Public Function FindPath()
Dim db As Database, rst As Recordset, rst2 As Recordset
Dim infile1, infile2, outfile, L As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("Table5", dbOpenDynaset)
Set rst2 = db.OpenRecordset("Table6", dbOpenDynaset)

Do While Not rst.EOF
infile1 = rst![Filepath]
infile2 = infile1
L = InStr(1, infile2, "#")
If L > 0 Then
   infile2 = Left(infile2, L - 1)
End If
outfile = Dir(infile2)
If Len(outfile) = 0 Then
   With rst2
    .AddNew
    ![Filepath] = infile
    .Update
   End With
End If
rst.MoveNext
Loop

rst.Close
rst2.Close

Set rst = Nothing
Set rst2 = Nothing
Set db = Nothing

End Function

You may modify the Code to replace the Input Table Name (Table5) & Field Name with your own. You must create a second table with the same Structure (Table6) to write out the missing file's list.

If your file's list is a HyperLink field the Code takes care of that.