1

I'm newbie . Currently I'm to trying to import the image [varbinary(max) as the data type] from sql server 2008 into the vba excel. I get "Type Mismatch" error.

Here I'm provide the code that I have

serverConn   'call the connection function

' open connection
objConnection.Open strConnectStr

Dim rs As ADODB.Recordset
Dim stm As ADODB.Stream

Set rs = New ADODB.Recordset
Set stm = New ADODB.Stream

Dim sqlquery As String

sqlquery = "SELECT [well_img] FROM well_image WHERE [img_id] = 2"

rs.Open sqlquery, objConnection, adOpenStatic, adLockOptimistic

stm.Type = adTypeBinary
stm.Open
stm.Write rs("well_img").Value   'write bytes to stream
stm.Position = 0

Sheet1.display_img.Picture = stm.Read   'this line is the problem ("Type Mismatch error")

stm.Close
rs.Close
objConnection.Close

Set stm = Nothing
Set rs = Nothing
Set objConnection = Nothing

The display_img is Image ActiveX control. Hopefully somebody can help me, I'm really need to know the answer. Thank you.

user3579827
  • 21
  • 1
  • 3
  • Gord's answer here (http://stackoverflow.com/questions/21443467/how-to-load-picture-into-ms-access-image-control-from-ado-recordset-field) suggests something like this should work, so it may be how you're referencing your image control that's the problem. Try `Sheet1.OLEObjects("display_img").Object.Picture = stm.Read` – Tim Williams Apr 28 '14 at 04:29
  • Thank you for the suggestion answer, but sadly the problem is still not solved. Do you have another suggestion? – user3579827 Apr 29 '14 at 05:21
  • You could try saving the image to a temp file and loading it from there. Are you sure the image control is compatible with the format of your image? What type of format is the image? – Tim Williams Apr 29 '14 at 05:41
  • I'm also thinking about that, but I'm decide to save the image as a binary type in sql server. I have done to upload/save the img into the sql server db. But I'm still fail to display the image. The type of image is .png and .jpg. Thank u for helping. – user3579827 Apr 29 '14 at 06:38
  • I meant save the image to a temp file after pulling it out of the database. – Tim Williams Apr 29 '14 at 14:44

0 Answers0