Open attachment through VBA

I have a table with an attachment field. The attachments are all pdf files. I am looking for a way to open one of the attachments through VBA by creating a recordset of the attachments, locating a specific file name, then opening the pdf. I have the first two parts done, and can successfully locate the pdf file name, I just can't find a way to open it.

So again, this is NOT an attachment in an Outlook email, and not a saved file; it is a pdf stored in an attachment field. I want to simulate the same thing as opening an attachment window in Access, navigating to the file name in the list, then either double-clicking it or clicking Open.

When the pdf is originally saved I want to capture the name in a log so I can reference it later (for instance MyJulyFile.pdf), then later save additional files in the attachemt field (MyAugFile.pdf, MySeptFile.pdf, etc), then go back and select the specific file name in my log, and have the code go to the table with the attachments, find it and open it.

Here is my code so far:

Sub OpenAttachment(ByRef rstCurrent As DAO.Recordset, ByVal FieldName As String, ByVal AttachmentName As String)
'This procedure will open an existing attachment already stored in an attachment field.

Dim rstChild As DAO.Recordset2

Set rstChild = rstCurrent.Fields(FieldName).Value ' the .Value for a complex field returns the underlying Recordset.
rstChild.MoveFirst

Do Until rstChild.EOF
If rstChild.Fields("FileName").Value = AttachmentName Then
MsgBox "Match found" 'The code to open the attachment would go here
Exit Do
Else
rstChild.MoveNext
End If
Loop

End Sub

1. Through a command button click event I create the recordset of the parent table that has the attachment field in it, then call the above sub and pass the recordset as the first argument, then pass the name of the attachment field as the second, then the name of the .pdf file as the third argument.

2. The code above then creates the rstChild recordset from the attachment field, iterates through the recordset and locates the matching name.

That all works fine so far. I just can't find the way to do the last step and open the pdf once the recordset cursor lands on it.

Any suggestions will be much appreciated.
Thanks

PDF can be opened only in Adobe software. I think you have to:

1. save the attachment pdf out to a folder
http://blogs.office.com/b/microsoft...ccess-2007.aspx

2. use hyperlink code to open file
http://allenbrowne.com/func-GoHyperlink.html

3. delete the pdf from folder
http://stackoverflow.com/questions/...g-a-file-in-vba

June7 - I appreciate the reply.

Yes I know that i can copy the attachment out to a temp folder to open it. What I am trying to do though is NOT have to do that, but rather simulate the same thing as opening the attachment window by (for instance) double-clicking the paper clip in the attachment field in the table, then either double-clicking or highlighting the specific attachment name and clicking Open. When done that way, Access does not copy the pdf out to a temp folder and open it there, it just opens it. I'm trying to find to way through code to reproduce this funtionality of just straight opening the attachment.

Actually I think the Open action from the Attachments dialog does save the document to a temp location then opens that file. This is supported by a check of the file properties in the Adobe app with the Attachment file opened from the dialog and seeing the Location '...\Temporary Internet Files\ACCDDxx\'

Although when I try to navigate to that folder with Windows Explorer, can't find it. I click on the displayed path in the Adobe properties and an Explorer window opens. Bizarre - some sort of 'virtual' folder?

Regardless, to replicate this behavior will have to save the attachment to a folder location then open the saved file. All code snippets I can find follow this.

Yes, all of the code examples I have found are also creating a "temp copy" of the attachemnt. If possible I am trying to avoid have a bunch of copies pile up when I already have the pdf files saved in my attachment field. And yes I know there is code I could use to turn around and delete the temp copy, but that all seems unnecessary as my thinking is there still should be a way to just straight open the file. I can do it through the Attachments dialog, but again after creating the recordset and landing on the correct file name in the recordset. I just haven't found the method or technique for opening.

I looked, and Access actually doesn't create any kind of "temp copy" when you open an attachment through the Attachments dialog, it just opens the file. I am still hoping to find a way to do this through code.

Thanks again for the replies.

I still say Access is saving a 'temp' copy of the attachment somewhere, even if it is 'virtual' and not on disk, and Adobe renders that file.

I can only suggest you implement the known code at this time and if you find alternative solution later, implement it. Look forward to seeing it.

I would be a little suspect of a conclusion based on only one study and not confirmed by other research.

I'm trying to find to way through code to reproduce this funtionality of justURLURLURLURL

yeah,i think so.Actually I think the Open action from the Attachments dialog does save the document to a temp location then opens that file.