How do I input/output an attachment data type in Access 2007 to an Oracle data type in 10g?

I am in the process of migrating an Access 2007 database to an Oracle 10g database and ran into an issue with one of the field conversions.

In Access I have some Attachment fields that hold Microsoft word .docx files. I used SQL Developer to do the migration and it suggested using the Blob datatype in Oracle 10g. Access crashes when trying to retrieve the Blob because it does not know how to see this type. What would some code be for converting the attachment to a blob and converting the blob back into an attachment. I have found nothing online for this. It is key that the Word documents are held in this database.
Who is Participating?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  In the past, BLOB's in JET were gotten with the GetChunk() method in DAO (and put in with the corresponding AppendChunk()).

  However the Attachment field type is one of those new MVF (Multi Value Fields), which Access does by creating a hidden system table in the DB (they are a many to one relationship with the main record even though it appears in the main field).

  All I could find yesterday was an importattach command.  I would assume there is an export as well, but I couldn't verify that and ran out of time.

  I also saw that FMS Total Source Book contained routines to read attachments, so it must be doable.

  And you will need to use an Access command of some type to get at them because Access compresses the data when it stores it (no OLE wrapper like in times past though), and your going to want just the un-compressed data.  So directly reading the BLOB field with GetChunk() won't be workable.

Nico BontenbalCommented:
Seems like you are not the only one with this problem. Try this on Google:
"ms access" blob
Have a quick look at these questions
I've been working with @chovis78 on resizing pictures that he spits back out of an attachment type.

Clearly, he and those he's working with know how to get attachments back out of a DB
@fyed's got a sample in the second question to work with

Once they're out, ramming them into Oracle
<It is key that the Word documents are held in this database.>
and not in a folder with a pointer to a path in the Oracle database is a different piece of the puzzle
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.