Our SQL Server stores order attachments in a deprecated Image
datatype column. We currently track orders in Excel using VBA ADODB recordsets. I'm wondering if it's possible to display the actual attachment instead of its binary form. Even a link that opens them in a browser would work.
Order table:
SELECT Order, Attachment, FileType
FROM OrderTable
Order | Attachment | FileType |
---|---|---|
1 | 0x255044462... | .png |
2 | 0x89504E470... | |
3 | 0xFFD8FFE00... | .jpg |
I've used something like this in the past and it works fine.
Whether it works for you will likely depend on exactly what's stored in that "attachment" field.
EDIT - just noticed that you also have PDF files as attachments. In that case writing to disk with the file extension and then opening the file in the default program should also work. See (eg) https://stackoverflow.com/a/18922262/478884 for how to do that.
Sub TestPic()
Dim rs As ADODB.Recordset, tmpFileName
'### connect to DB and open a recordset `rs` ###
If Not rs.EOF Then
tmpFileName = TempName() & rs.Fields("FileType").Value
'write binary field content out to a file on disk
With New ADODB.Stream
.Type = adTypeBinary
.Open
.Write rs.Fields("Attachment").Value 'write bytes to stream
.SaveToFile tmpFileName & , adSaveCreateOverWrite 'save content to file
End With
' (Eg) open `tmpFileName` using default application
' Or you can switch what you do based on the type of file
CreateObject("Shell.Application").Open(tmpFileName)
End If 'any record(s)
End Sub
'Return a path and filename where content can be written
Function TempName()
With CreateObject("Scripting.FileSystemObject")
TempName= .GetSpecialFolder(2) & "\" & .GetTempName '2=Tempfolder
End With
End Function