Unfiltered HTML
Unfiltered HTML

Using VBA code to open a file stored in the new Access 2007 Attachment field

by
on August 30
Someone asked me about using the Attachment data type in Access 2007 for a specific task. They wanted to use it as a simple data type where it would only be used to hold 1 file per record, and they wanted to be able to open the file in an external application using a button on a form instead of using the Attachment control on a form.  Given these constraints, I was created a simple VBA function for doing just that:

Public Function OpenFirstAttachmentAsTempFile(ByRef rstCurrent As DAO.Recordset, ByVal strFieldName As String) As String

    Dim rstChild As DAO.Recordset2
    Dim fldAttach As DAO.Field2
    Dim strFilePath As String
    Dim strTempDir As String

    strTempDir = Environ("Temp") ' Get the Temp directory from the environment variable.
    If Right(strTempDir, 1) <> "\" Then strTempDir = strTempDir & "\" ' Make sure the path always ends with a backslash.
        Set rstChild = rstCurrent.Fields(strFieldName).Value ' the .Value for a complex field returns the underlying recordset.
        strFilePath = strTempDir & rstChild.Fields("FileName").Value ' Append the name of the first (and only) attached file to temp dir.
        If Dir(strFilePath) <> "" Then ' the file already exists--delete it first.
        VBA.SetAttr strFilePath, vbNormal ' remove any file attributes (e.g. read-only) that would block the kill command.
        VBA.Kill strFilePath ' delete the file.
    End If

    Set fldAttach = rstChild.Fields("FileData") ' The binary data of the file.
    fldAttach.SaveToFile strFilePath
    rstChild.Close ' cleanup
    VBA.Shell "Explorer.exe " & Chr(34) & strFilePath & Chr(34), vbNormalFocus ' Use Windows Explorer to launch  the file.

End Function 'OpenFirstAttachmentAsTempFile


Then I wrote this function just to test it in a database that has a Table1 with an Attachment field named "Files" in which I had already placed a file:

Public Function TestOpenFirstAttachmentAsTempFile()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
   
    Const strTable = "Table1"
    Const strField = "Files" ' Attachment field in Table1

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strTable)
    'rst.MoveNext ' Uncomment this line to go to the 2nd row in the Table.
    OpenFirstAttachmentAsTempFile rst, strField
    rst.Close
End Function

By using the VBA.Shell command with Explorer.exe, the file will be opened just as if you double clicked it from Windows Explorer!

See my previous blog post about attachments for more information about accessing attachments from DAO.
Tags:
, , , ,

Office Blogs Comments

Comments: (3) Collapse

Comments

Comments: (loading) Collapse