?
Solved

Exporting Image Column To Access mdb file

Posted on 2011-09-08
11
Medium Priority
?
343 Views
Last Modified: 2012-05-12
Hello,

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?
0
Comment
Question by:bnrtech
[X]
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
11 Comments
 
LVL 5

Expert Comment

by:DavidMorrison
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?


Thanks

Dave
0
 
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)
http://support.microsoft.com/kb/173308.

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

;-)

JeffCoachman
0
 

Author Comment

by:bnrtech
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?
0
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
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
0
 

Author Comment

by:bnrtech
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
0
 

Author Comment

by:bnrtech
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.
0
 
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?
0
 

Author Comment

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

Author Comment

by:bnrtech
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.
0
 
LVL 74

Accepted Solution

by:
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...
0
 

Author Comment

by:bnrtech
ID: 36970038
Thanks boag
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

764 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