Welcome to MSDN Blogs Sign in | Join | Help

Microsoft Access Team Blog

Get product announcements, tips and tricks, and news directly from the team @ Microsoft.
Using VBA code to open a file stored in the new Access 2007 Attachment field
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.
Posted: Thursday, August 30, 2007 10:30 AM by James K. Rivera

Comments

MSDN Blog Postings » Using VBA code to open a file stored in the new Access 2007 Attachment field said:

PingBack from http://msdnrss.thecoderblogs.com/2007/08/30/using-vba-code-to-open-a-file-stored-in-the-new-access-2007-attachment-field/

# August 30, 2007 3:38 PM

Oli-S said:

...and yet another way to interact with it in this post:

http://www.access-freak.com/blog.html#BlogEntryAugust200705

# August 30, 2007 5:22 PM

emmanuel said:

I WANT TO KNOW MORE ABOUT CONSTUCTING VBA

# September 3, 2007 5:37 AM

Zac Woodall said:

Emmanuel, for general VBA questions, check out our communitites here:

http://blogs.msdn.com/access/archive/2007/07/31/access-communities.aspx

# September 4, 2007 4:28 PM
New Comments to this post are disabled
Page view tracker