Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Exporting Image Column To Access mdb file

Posted on 2011-09-08
Medium Priority
Last Modified: 2012-05-12

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?
Question by:bnrtech
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4

Expert Comment

ID: 36505877
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?


LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36507393
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...



Author Comment

ID: 36510955
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?
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36511861
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

Author Comment

ID: 36891907
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

Author Comment

ID: 36891967
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.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36893874
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?

Author Comment

ID: 36913405
I have been out of the office. WIll circle back on this one shortly.

Author Comment

ID: 36916846
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.
LVL 74

Accepted Solution

Jeffrey Coachman earned 2000 total points
ID: 36918043
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...

Author Comment

ID: 36970038
Thanks boag

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question