sql-serverexcelvbaadodb

Can you display a SQL Server Image datatype column in Excel?


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... .pdf
3 0xFFD8FFE00... .jpg

Solution

  • 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