2

I have an Access 2010 odbc front end with a SQL Server 2012 back end. My images (.bmp) are stored as Varbinary(Max). When I insert an image using my bound object frame in an Access form, the image displays on the form and report perfectly. I had a large number of pictures to bring into SQL so I used the following code in SQL Server Management Studio:

UPDATE dbo.Photos_Observations 
SET [Photo] = (SELECT BulkColumn 
FROM Openrowset( Bulk '\\serverIP\servername\020.BMP', Single_Blob) as MyImage) 
WHERE PhotoTableID =391

This worked well and all 600+ pictures are now in SQL Server. BUT the ones I added this way don't show up on my Access form or report. When I click on the Bound Object Frame I get Error number 502753 "A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX control."

Why does it work one way and not the other? How can I get the images to display?

HansUp
  • 95,961
  • 11
  • 77
  • 135
LauraNorth
  • 182
  • 3
  • 11

1 Answers1

2

It looks like you bulk-inserted the raw binary image data directly from the BMP file into the VARBINARY column using T-SQL. Therefore, those images don't have the "OLE wrapper" that is added when an image is inserted via a Bound Object Frame on an Access form.

See the my answer to a related question for a way to automate the insertion of "OLE wrapped" images. (It's a bit clunky, but still better than doing it all by hand.)

Another approach is to use an Image control instead of a Bound Object Frame, and store the images as raw binary data instead of as "OLE wrapped" objects. That is increasingly becoming the preferred way to handle images being stored in the database itself. (OLE objects can be a nuisance, especially when working with applications other than Access.)

Community
  • 1
  • 1
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Thank you for your quick response! Is there a way to programmatically add the OLE wrapper to my existing data? – LauraNorth Jun 15 '15 at 21:09
  • If you still have the BMP files you could just remove the raw binary data from the SQL table and then re-insert the images via a Bound Object Frame using Access automation as described in my other answer. Otherwise you could dump the raw binary data back out of the SQL table into individual BMP files before removing the binary data from the table. – Gord Thompson Jun 15 '15 at 21:20