469,127 Members | 1,279 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,127 developers. It's quick & easy.

Print Access 2007 Attachment with VBA


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

Expand|Select|Wrap|Line Numbers
  1. Public Function OpenFirstAttachmentAsTempFile(ByRef rstCurrent As DAO.Recordset, ByVal strFieldName As String) As String 
  3.     Dim rstChild As DAO.Recordset2
  4.     Dim fldAttach As DAO.Field2
  5.     Dim strFilePath As String
  6.     Dim strTempDir As String
  8.     strTempDir = Environ("Temp") ' Get the Temp directory from the environment variable.
  9.     If Right(strTempDir, 1) <> "\" Then strTempDir = strTempDir & "\" ' Make sure the path always ends with a backslash.
  10.         Set rstChild = rstCurrent.Fields(strFieldName).Value ' the .Value for a complex field returns the underlying recordset.
  11.         strFilePath = strTempDir & rstChild.Fields("FileName").Value ' Append the name of the first (and only) attached file to temp dir.
  12.         If Dir(strFilePath) <> "" Then ' the file already exists--delete it first.
  13.         VBA.SetAttr strFilePath, vbNormal ' remove any file attributes (e.g. read-only) that would block the kill command.
  14.         VBA.Kill strFilePath ' delete the file.
  15.     End If
  17.     Set fldAttach = rstChild.Fields("FileData") ' The binary data of the file.
  18.     fldAttach.SaveToFile strFilePath
  19.     rstChild.Close ' cleanup
  20.     VBA.Shell "Explorer.exe " & Chr(34) & strFilePath & Chr(34), vbNormalFocus ' Use Windows Explorer to launch  the file.
  22. 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:
Expand|Select|Wrap|Line Numbers
  1. Public Function TestOpenFirstAttachmentAsTempFile()
  2.     Dim dbs As DAO.Database
  3.     Dim rst As DAO.Recordset
  5.     Const strTable = "Table1"
  6.     Const strField = "Files" ' Attachment field in Table1
  8.     Set dbs = CurrentDb
  9.     Set rst = dbs.OpenRecordset(strTable)
  10.     'rst.MoveNext ' Uncomment this line to go to the 2nd row in the Table.
  11.     OpenFirstAttachmentAsTempFile rst, strField
  12.     rst.Close
  13. 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".
Feb 19 '09 #1
2 9074
374 Expert 256MB

Hey crasyhouse,

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

See if this will help?



Joe P.
Feb 19 '09 #2
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!
Jul 2 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

37 posts views Thread by jasmith | last post: by
reply views Thread by ProgrammerChicago | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.