Microsoft Access: Show an image from a Sql Server varbinary(max) field in an Access Image control

Platform:Microsoft Access
Task:Show an image from a Sql Server varbinary(max) field in an Access Image control
Discussion:
You need to show an image from a Sql Server varbinary(max) field in an Access Image control
Example:
' Form_Current
' Created 2012-Jan-06, by Nick Bywater
Private Sub Form_Current()
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim par As ADODB.Parameter
    Dim rst As ADODB.Recordset
    Dim varImage As Variant
    Dim strSQL As String

    On Error GoTo ErrorHandler

    ' The following assignment also sets .PictureData to NULL.
    Me.imgSurveyImage.Picture = "(none)"

    If Not Me.NewRecord Then

        Set cnn = New ADODB.Connection

        cnn.ConnectionString = "Provider=SQLOLEDB;" & _
                               "Data Source=INPYUGAMS08SQL\Nuna_dev;" & _
                               "Initial Catalog=CompositionCountSurveys;" & _
                               "Integrated Security=SSPI"

        Set cmd = New ADODB.Command

        cnn.Open

        cmd.ActiveConnection = cnn
        cmd.CommandType = adCmdText

        strSQL = "SELECT SurveyImage " & _
                 "FROM dbo.SurveyImages " & _
                 "WHERE ImageID = ?"

        cmd.CommandText = strSQL
        cmd.NamedParameters = True

        Set par = cmd.CreateParameter("ImageID", adVarWChar, adParamInput, 50, Me.ImageID)
        cmd.Parameters.Append par

        Set rst = New ADODB.Recordset

        rst.CursorLocation = adUseClient
        rst.LockType = adLockReadOnly
        rst.CursorType = adOpenForwardOnly

        rst.Open cmd

        varImage = rst.Fields("SurveyImage").Value

        If Not IsNull(varImage) Then
            Me.imgSurveyImage.PictureData = varImage
        End If

        rst.Close
        Set rst = Nothing
        cnn.Close
        Set cnn = Nothing
        Set cmd = Nothing
        
    End If
    
ExitSub:
    Exit Sub
    
    
ErrorHandler:

    Select Case Err.Number
        Case Else
            MsgBox "Error #: " & Err.Number & vbCrLf & _
                   "Description: " & Err.Description, vbCritical, "SurveyImage Error"
    End Select
    Resume ExitSub
    
End Sub
Back to Index