Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

Posted on 2011-04-20
Medium Priority
Last Modified: 2012-05-11
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.
Question by:kkusic1
LVL 22

Assisted Solution

by:Nico Bontenbal
Nico Bontenbal earned 336 total points
ID: 35438392
Seems like you are not the only one with this problem. Try this on Google:
"ms access" blob site:forums.oracle.com
LVL 59

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 332 total points
ID: 35439698

  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.

LVL 26

Assisted Solution

Nick67 earned 332 total points
ID: 35445150
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

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

571 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