Checking if a file is linked in a database

Question

I have an Access database that stores information about word documents. One of the rows contains a link to that file. Due to some complications with the database, I started writing a code that checks if the links are valid and lead to an existing file:

Private Sub LinkCheck()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim ind As Integer
    Dim errStr As String

    Set db = CurrentDb
    Set rs = db.OpenRecordset("Files", dbOpenTable)
    rs.MoveFirst

    With rs
        For ind = 0 To (rs.RecordCount - 1)
        On Error Resume Next
            If Dir(rs!Hyperlink) = "" Then
                If Err.Number = 52 Then
                    Err.Clear
                    errStr = errStr & rs!FileName & " RUNTIME" & vbNewLine
                Else
                    errStr = errStr & rs!FileName & vbNewLine
                End If
            End If
        rs.MoveNext
        Next ind
    End With
    If errStr <> "" Then MsgBox (errStr)

This sub checks if the link leads to a valid file. If the link is valid, but the file does not exist, the sub adds the file's name from the database to a message box, and if the link itself is invalid, it adds the file name to the same message box with a "RUNTIME" besides it, to indicate a Runtime Error 52.

This sub works well, and now I am trying to write a code that performs the opposite action, going through the files in the relevant folders and sub folders and checking if that file is linked in the database.

This is where I'm stuck, as I am relatively new to VBA and I am not sure how to tackle this issue. Is there a way to use a similar sub to perform this action?

So first you need to figure out how to loop through folders. This is fairly common requirement and many examples available. https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba and http://allenbrowne.com/ser-59.html. Pass filename to a function that checks if it is in a record, perhaps with a DLookup(). — June7, Jan 06 '21 at 20:05
June7 provided you with very useful link resources that should give you base to implement what you need. I'm adding only some info about [close vs set variables to nothing](https://stackoverflow.com/questions/2542449/whats-the-difference-between-rs-close-vs-rs-nothing-in-a-recordset), best practices that your code seems to be in need. — Marcelo Scofano Diniz, Jan 06 '21 at 21:16

Checking if a file is linked in a database

0 Answers0