How to work with Attachment data type in VBA

Question

Is it possible to view the size of an attached file in a field of an Access table? For example, using <field>.FileName or <field>.FileType I can view the name or extension of the attached file. Is there a similar feature for file size?

Answer 1

No, not natively, but you should be able to get this via VBA.

You could try something like:

Dim rs As DAO.Recordset2
Dim rsAttach As DAO.Recordset2
Dim lFileSize As Long

Set rs = CurrentDb.OpenRecordset("YourTableName")
Set rsAttach = rs.Fields("YourAttachmentFieldName").Value
If Not rsAttach.EOF Then
    lFileSize = LenB(rsAttach.Fields("FileData").Value)
    MsgBox "File size: " & lFileSize & " bytes"
End If
Set rsAttach = Nothing
Set rs = Nothing

Or perhaps

    Dim rs                    As DAO.Recordset2
    Dim rsAttach              As DAO.Recordset2
    Dim sFilename             As String
    Dim lFileSize             As Long

    Set rs = CurrentDb.OpenRecordset("Employees")
    Debug.Print "Filename", "Size (b)"

    Do While Not rs.EOF
        If Not IsNull(rs.Fields("Pics").Value) Then
            Set rsAttach = rs.Fields("Pics").Value
            Do While Not rsAttach.EOF
                sFilename = rsAttach.Fields("FileName").Value
                lFileSize = LenB(rsAttach.Fields("FileData").Value)
                Debug.Print sFilename, lFileSize & " bytes"
                rsAttach.MoveNext
            Loop
            rsAttach.Close
        End If
        rs.MoveNext
    Loop
    rs.Close
    Set rsAttach = Nothing
    Set rs = Nothing

The file size calculated here will be very close to the real value, but never 100% the same as via Windows Explorer as Access seems to add a small amount of info to the attachment.

Another solution might be to save the file size when you upload/save the attachment originally. Then you would have the accurate value.