Windows 7 X64 Using VBA code to open a file stored
 

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,microsoft Office 2010 Serial, 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,Windows 7 X64, 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,Office Professional Plus 2007, 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,Office Professional Plus 2010, 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,Microsoft Office Home And Business 2010!
See my previous blog post about attachments for more information about accessing attachments from DAO. <div