Solved

Exporting Image Column To Access mdb file

Posted on 2011-09-08
11
296 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
  • 6
  • 4
11 Comments
 
LVL 5

Expert Comment

by:DavidMorrison
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:bnrtech
Comment Utility
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
Comment Utility
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
Comment Utility
I have been out of the office. WIll circle back on this one shortly.
0
 

Author Comment

by:bnrtech
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks boag
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now