UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Programmatically Exporting OLE Attachments, Access 2000    
 
   
mzafkismugi
post Jun 5 2019, 07:10 AM
Post#1



Posts: 2
Joined: 5-June 19



I have a huge database with a lot of OLE attachments which I'm trying to extract. So far I tried:

1. https://officeaccelerators.wordpress.com/20...access-to-disk/
2. http://www.lebans.com/oletodisk.htm

Since both approaches didn't work for me I decided to dig in but I'm stuck. What I know so far:

1. MS Access 2000 as a frontend
2. The attachments were created using "Create from File" dialog
3. SQL Server 2010 is the backing database.
4. The table is called contact_items_buf
5. There are two columns: nr and buf. The actual file seems to be in the buf column.
6. Type of column buf is image
7. It appears the original filenames are not preserved (can't say for sure)
8. The value stored in the column is not the file itself, I tried saving that as a binary file with the correct extension, but Word was not able to open it (or I've been doing it wrong)


I can open attachments in Access just fine but manually saving ~100k files is going to take ages. Is there any hope for me or I'll have to do it manually?
This post has been edited by mzafkismugi: Jun 5 2019, 07:11 AM
Go to the top of the page
 
GroverParkGeorge
post Jun 5 2019, 07:44 AM
Post#2


UA Admin
Posts: 35,532
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

First a bit of housekeeping.

"MS Access 2000"? That version has been out of support for a long time. Just wondering if there's a reason to hanging on to it for so long? Why not a more modern version?

"SQL Server 2010 is the backing database."

There is no version of SQL Server for 2010. Maybe you can double-check that? It might not matter, but I'm curious where "2010" comes from.

When you say "extract", I assume you mean you want to save the attachment as a stand-alone file on a disk? And I further assume these would be .doc files ("...but Word was not able to open it ")

You didn't mention whether and how you work with these attachments in the mdb. How's that being done?

Knowing more precisely what you have to work with will help us offer more appropriate suggestions .

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
mzafkismugi
post Jun 5 2019, 08:48 AM
Post#3



Posts: 2
Joined: 5-June 19



QUOTE
"MS Access 2000"? That version has been out of support for a long time. Just wondering if there's a reason to hanging on to it for so long? Why not a more modern version?

Well, a customer approached us, saying they want to migrate away from this whole stack. Apparently they had the application around from 1993 and only ever upgraded in 2000.

QUOTE
There is no version of SQL Server for 2010. Maybe you can double-check that? It might not matter, but I'm curious where "2010" comes from.

You are correct, my mistake here: the actual version is Microsoft SQL Server 2000 - 8.00.760 (Intel X86).

QUOTE
When you say "extract", I assume you mean you want to save the attachment as a stand-alone file on a disk? And I further assume these would be .doc files ("...but Word was not able to open it ")

You are almost correct. I want to extract those to files on a disk. About the file type - I know for certain that the attachments include: doc, docx, pdf, jpg, png and probably some other common formats

1. Find Contact to which you wish to add attachment
2. Press Insert button
3. Select Create from File option
Attached File  1.JPG ( 28.07K )Number of downloads: 0

4. Browse for the file and click OK
5. At this point the attachment gets stored in the database

From now on, you can just select the attachment, press Open, and it would open up with the associated application. So if you selected a doc file, it would open in Word, if a txt file was selected then it would open in Notepad etc.. That's why I assumed it's and OLE object.

QUOTE
You didn't mention whether and how you work with these attachments in the mdb. How's that being done?

That, I don't know. I'll attach the procedures linked with the Insert and Open buttons below, maybe that will help. I have noticed one thing about those procedures: "Global_ShowOLE" nor "Global_ShowOLE2" forms are present in the Access application code I have received. Yet this somehow works.

Attached File  insert_button_procedure.txt ( 1.32K )Number of downloads: 4

Attached File  open_button_procedure.txt ( 2K )Number of downloads: 4

This post has been edited by mzafkismugi: Jun 5 2019, 08:49 AM
Go to the top of the page
 
GroverParkGeorge
post Jun 5 2019, 09:02 AM
Post#4


UA Admin
Posts: 35,532
Joined: 20-June 02
From: Newcastle, WA


Thanks. That's one of the wonderful things about Access, I guess. Even something more than 25 years old still works... Not a lot of software makes that claim.

I think we can find a stored procedure in SQL Server that might do this for a one time project a little better.

But if the embedded files can be any one of those formats, the question is going to be getting each one out correctly. Can you identify that?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Jun 5 2019, 09:13 AM
Post#5


UA Admin
Posts: 35,532
Joined: 20-June 02
From: Newcastle, WA


Maybe this discussion on StackOverflow will be a good starting place.

I modified the Stored Procedure offered in that discussion to suit my local SQL Server instance. It seems to do the job, but you'll also need to customize to suit YOUR environment. Here's another link that might be helpful in figured out how to modify the Stored Proc.
This post has been edited by GroverParkGeorge: Jun 5 2019, 09:36 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ADezii
post Jun 5 2019, 02:09 PM
Post#6



Posts: 2,553
Joined: 4-February 07
From: USA, Florida, Delray Beach


I've got Leban's Code to work but my question is will it work on a Linked SQL Server Table?
Go to the top of the page
 
ADezii
post Jun 5 2019, 05:37 PM
Post#7



Posts: 2,553
Joined: 4-February 07
From: USA, Florida, Delray Beach


Got the Code working on the Northwnd SQL Server Database (2014). The contents of the [Photo] Field stored as Image in dbo.Employees were extracted to a designated Folder. If you are interested let me know, I just need to now strip the DB (Leban's) of any unnecessary Code (a lot of it) and remove any DB Objects (lot of them too) that are not relevant.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    23rd August 2019 - 02:46 PM