How do I display an image from Sql Server with Microsoft Access?

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

You should use `varbinary(max)` as `image` is deprecated: http://stackoverflow.com/questions/444072/varbinary-vs-image-sql-server-data-type-to-store-binary-data — Tom Chantler, Oct 14 '11 at 15:32
just to clarify, when an OLE field is bound to a 'Bound OLE Control' in a form, and a file is pasted into the control, the file is stored in a binary access-specific OLE wrapper. the control also expects the wrapper when displaying files/images, but if the file was saved into the table with code rather than pasting, the wrapper is not there. using unbound controls as in the answers below bypasses the issue as these controls don't require the OLE wrapper. — Ben McIntyre, Nov 02 '14 at 03:40

Fionnuala · Answer 1 · 2014-10-29T00:39:58.157

8

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.

edited Oct 29 '14 at 00:39

answered Aug 31 '14 at 07:10

Fionnuala

90,370
7
114
152

nvarbinary(MAX) is the data type that I used in SQL Server. I added an image Control to the form and did what Fionnuala explained above. – Dave Stuart Sep 13 '14 at 21:05
figured it myself it works. Me.Image0.PictureData = Recordset("picture").Value – Shimon Doodkin Oct 28 '14 at 19:16

HK1 · Accepted Answer · 2011-10-15T18:59:16.820

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

How do I display an image from Sql Server with Microsoft Access?

2 Answers2

Linked