Exporting Image Column To Access mdb file


I have a database that is being exported to an access file. Two tables are causing the export to fail though. They both contain a column that is of type image. The other fields are ints and varchars in type. For some reason certain records in these tables are causing errors during export.

The problem is that the error only states "An OLE DB error has occurred."

I have tried inspecting the images and rows in question but cannot find any issues with them. They are editable and viewable in all of the normal ways. Does anyone have any ideas on what may be going wrong here, and how I can work around it?
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
Then I am stumped.

It seems that something was changed by MS and this no longer works.

You may want to contact MS directly on this...
Hi, an imagine type column is effectively just a BLOB. I dont know access that well, but does it support BLOB columns?

The other issue is that maybe the OLE DB driver doesnt know how to convert an image type to something access can read or that the size of the column is too large to transfer.

Generally I opt away from embedding images into the database, is there a way you can extract them and just store a file name instead?


Jeffrey CoachmanMIS LiasonCommented:
DavidMorrison is on the right track...

But we need to clear up a few things...
What version of Access are you using?
What "format" is the DB in (.mdb or .accdb)?

In an Access 2007 formatted database (.accdb) there can be an OLE field or an "attachment" field.
I seriously doubt that either of these MS Access specific Datatypes will accept the SQL "Image" field data directly.
(as evidenced by your Q here...)

There may be some utility out there that someone created that will aid in doing this, but I don't know of any off the top of my head.
So here GIYF

You can see this link for possibly displaying this data in IE (or possibly a web browser control)

There are many Experts who have investigated these topics, so let's see if any chime in and add some insight...


Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

bnrtechAuthor Commented:
Hi David,

When building an export of the table with images SQL chooses to convert the column to type OLE Object so, while it doesnt store BLOBs natively it has no issue accepting them as OLE Objects.

I also thought maybe certain images were too large. I redirected the error rows into another access file and tried downsizing them to no avail. And to repeat, only a few rows are causing errors. Most others are exported fine.

The image data must continue to be stored in an access file for for processing by a 3rd party.

To JeffCoachman

I am using Access 2007 and the access file is formatted as an mdb file.

Might be worth adding that under SQL 2005 on a 32bit server this process worked fine. The new server is 64bit and running SQL 2008 R2 though. Does this help?
Jeffrey CoachmanMIS LiasonCommented:
Most of the time the Export goes from Access-->SQL.

So it may be that the SQL "image" datatype changed in the newer version?

Also note that you are using a "format" of Access that is old (.mdb=2002-2003, or almost 8 years old) and may not support the newer "image" SQL datatype
bnrtechAuthor Commented:
I have been out of the office for a bit. Getting back in to the swing of things. Will check it out shortly and circle back. Thanks
bnrtechAuthor Commented:
Forgot to mention that the following.

The image type column existed on both the 2005 and 2008 versions of the server. They table definitions have not changed.

Also worth noting is that lately when the export fails, I simply rerun and it succeeds.
Jeffrey CoachmanMIS LiasonCommented:
Then all I can suggest is that yo make sure you have all your updates and Service Pocks installed for:
Office *and* Windows on all the machines

Are you doing the import via a wizard or is this something custom?
bnrtechAuthor Commented:
I have been out of the office. WIll circle back on this one shortly.
bnrtechAuthor Commented:
All service packs and updates for WIndows, SQL Server, and Office 2007 are installed. The export has been tried with an SSIS package created through a wizard and from a custom one as well. Each time with the same issue.
bnrtechAuthor Commented:
Thanks boag
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.