How do I display an image from Sql Server with Mic

Question

I upsized an Access 2007 database to SQL Server 2008 R2. The images are in SQL Server as image type. Access has link to the table containing the image. When I try to display from within Access, it won't do it. It still has the OLE Object wrapper.

How can I get that image and display it on my forms in Access? I do not have the option, at the moment, to remove the images, put them in a directory and point to them (the best way I know but not an option). I need to read the image / blob file directly from SQL Server and display it on an Access form.

Thank you for any ideas.

I saw this but it did not help:

How to display image from sql server in ms access

http://access.bukrek.net/documentation looks like the file in folder method

Answer 1

Below is a function I have successfully used called BlobToFile. And I also posted the code that I use to test it. The picture gets dumped to a so-called temp file but its not truly temp because it isn't in the temp directory. You can manually delete the image file or else you'll have to write it to your temp folder instead. Then I have an image control where I display the picture.

Private Sub Command1_Click()
    Dim r As DAO.Recordset, sSQL As String, sTempPicture As String
    sSQL = "SELECT ID, PictureBlobField FROM MyTable"
    Set r = CurrentDb.OpenRecordset(sSQL, dbSeeChanges)
    If Not (r.EOF And r.BOF) Then
        sTempPicture = "C:\MyTempPicture.jpg"
        Call BlobToFile(sTempPicture, r("PictureBlobField"))
        If Dir(sTempPicture) <> "" Then
            Me.imagecontrol1.Picture = sTempPicture
        End If
    End If
    r.Close
    Set r = Nothing
End Sub


'Function:  BlobToFile - Extracts the data in a binary field to a disk file.
'Parameter: strFile - Full path and filename of the destination file.
'Parameter: Field - The field containing the blob.
'Return:    The length of the data extracted.
Public Function BlobToFile(strFile As String, ByRef Field As Object) As Long
    On Error GoTo BlobToFileError

    Dim nFileNum As Integer
    Dim abytData() As Byte
    BlobToFile = 0
    nFileNum = FreeFile
    Open strFile For Binary Access Write As nFileNum
    abytData = Field
    Put #nFileNum, , abytData
    BlobToFile = LOF(nFileNum)

BlobToFileExit:
    If nFileNum > 0 Then Close nFileNum
    Exit Function

BlobToFileError:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, _
           "Error writing file in BlobToFile"
    BlobToFile = 0
    Resume BlobToFileExit

End Function

Answer 2

Since Access 2010, you can use the PictureData property to store and display images from SQL Server. You will need a bound control for an SQL Server data type varbinary(max), which can be hidden, and an unbound Image control in MS Access. You can now simply say:

Private Sub Form_Current()
    Me.MSAccessImageControl.PictureData = Me.SQLServerImage
End Sub

And vice versa. You will need to add some error management to that, but very little else.