Solved

Exporting Image Column To Access mdb file

Posted on 2011-09-08
11
333 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 500 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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

730 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