How Do I Display An Image From Sql Server With Microsoft Access?

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 fro

Solution 1:

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:

PrivateSub Form_Current()
    Me.MSAccessImageControl.PictureData = Me.SQLServerImage
EndSub

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

Solution 2:

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.

PrivateSub Command1_Click()
    Dim r As DAO.Recordset, sSQL AsString, sTempPicture AsString
    sSQL = "SELECT ID, PictureBlobField FROM MyTable"Set r = CurrentDb.OpenRecordset(sSQL, dbSeeChanges)
    IfNot (r.EOF And r.BOF) Then
        sTempPicture = "C:\MyTempPicture.jpg"Call BlobToFile(sTempPicture, r("PictureBlobField"))
        If Dir(sTempPicture) <> ""ThenMe.imagecontrol1.Picture = sTempPicture
        EndIfEndIf
    r.Close
    Set r = NothingEndSub'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.PublicFunction BlobToFile(strFile AsString, ByRef Field AsObject) AsLongOnErrorGoTo BlobToFileError

    Dim nFileNum AsIntegerDim abytData() AsByte
    BlobToFile = 0
    nFileNum = FreeFile
    Open strFile ForBinary Access Write As nFileNum
    abytData = Field
    Put #nFileNum, , abytData
    BlobToFile = LOF(nFileNum)

BlobToFileExit:If nFileNum > 0Then Close nFileNum
    ExitFunctionBlobToFileError:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, _
           "Error writing file in BlobToFile"
    BlobToFile = 0Resume BlobToFileExit

EndFunction