Print Access 2007 Attachment with VBA

[crazyhouse]

Hi,

I dont even know where to start on this one. I found this on the net (everything in italics are the authors comments):

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!

But i would like to take it one step farther and print it with the default printer. Does anybody think that is possible, or even worth doing. Basically i have documents attached (PDF documents), and want them to print out as the last 2 pages of a report (actually printed to a pdf document, if that helps at all).

Again, as i always say "Any help is greatly appreciated".

[PianoMan64]

Hi,

I dont even know where to start on this one. I found this on the net (everything in italics are the authors comments):

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!

But i would like to take it one step farther and print it with the default printer. Does anybody think that is possible, or even worth doing. Basically i have documents attached (PDF documents), and want them to print out as the last 2 pages of a report (actually printed to a pdf document, if that helps at all).

Again, as i always say "Any help is greatly appreciated".

Hey crasyhouse,

I found this link about being able to print batches of PDF files from OLE or DDE.

See if this will help?

http://support.adobe.com/devsup/devsup.nsf/docs/51412.htm

Thanks,

Joe P.

[Worsty]

create pdf from Access report and add pdf attachments

I was wondering if anyone had an answer for this. I can run the code in this post and it works great to open assigned attachments from the table (pdf), but what i want to do also is:

First run the access reports, then either save these Access reports as a pdf or open the pdf attachments per record and create one document as a pdf. Can anyone help me out on this? Thanks so much!