OLE Object to linked

[Paulsburbon]

Hello,

I am currently working on a database that I did not create. It has 4000 records all with a field for a ole object where a picture is stored in the database. Since this is making the database 500mb I need to change it. How do I make this change. I would create a text field in the table with the path but with all the pictures in the database how do I access them and then move them to a folder on a network share while keeping the picture linked to that record? Thanks for your time in helping me out.

Paul

 

[DCrake]

Here is a link to a Demo this is a 3 part exercise you need to look closely at step 3 which deals with extracting ole's to files. If you need further help then get back to me.

Send me a few records in the table to see if the format can be used.

 

[Paulsburbon]

David,
Wow. It was a bit overload for me but I think this was the code I was looking for to put into my database:

<code>
Dim rs As New ADODB.Recordset

With MasterDbConn
rs.Open "SELECT * FROM TblEmbeddedObjects WHERE fldDocumentName ='" & Me.LstEmbedded & "'", MasterDbConn, adOpenStatic, adLockReadOnly, adCmdText
If Not rs.EOF And Not rs.BOF Then
BlobToFile rs("fldDocument"), strFile, rs("fldDocument").ActualSize
DoEvents
End If
rs.Close
End With
Set rs = Nothing
</code>

If I adjust this to my connection and SQL statement I would be good to go or am I missing something or not understanding it correctly?

Thank you again for the quick response!

Paul

 

[DCrake]

If you follow what happens in steps one and two you will be able to understand step 3 easier.

Essentially with you situation you need to first have a field that will become the path and filename of the embedded document. Then when the process runs the BlobToFile routine it knows where to save the file and the name to save it to. If this is over your head or need some professional help I would be only to happy to help you.

Would need a few same records first to test the saved format of the embeded objects.

 

[Paulsburbon]

David,

So I used this code and your blob module.

Dim rs As New ADODB.Recordset


rs.Open "SELECT productID, picture FROM products WHERE productID = 5", CurrentProject.Connection
If Not rs.EOF Then
BlobToFile rs("picture"), rs("productID"), rs("picture").ActualSize
DoEvents
End If
rs.Close

Set rs = Nothing
DoEvents
MsgBox "File has been extracted", vbInformation + vbOKOnly, "Operation Completed"


This outputs a file with the filename of 5 in "my documents". However I cannot open this file. If it just exports the picture to a path that is set in stone and uses the ID feild to get the name I am set I will just update the new field "Picture Location" to the path and file name that is the productid. Did I get close or did I strip too much code off of it?

Paul

 

[DCrake]

What types of files were stored in the table? compared to what Access is saving them as in MyDocuments?

Like I have said before having eyes on the file will be better for me to offer you help.

Also how were the files embedded into the table in the first place?

 

[Paulsburbon]

Hi David,

I am not sure of the file types. They are all pictures that display in a picture box on a form in the database.

Access is saving them without a file extension. I double checked to make sure that I am viewing file extension and I am set up that way.

I have no idea how they were added to that database in the first place. I'm coming into this project after many different people had thier hands on this database and you should see the rest of the tables. This is one small part of it that I could not figure out. The rest I have been attempting to normalize the database as much as possible before I even try to fix the front end.

Here is a sample of the product table. .. ok I cannot figure out how to post a file on here. Let me poke around and do it on the next post.

 

[Paulsburbon]

ok I was using quick reply. Here is the database.

David thank you for taking the time to help me out.

 

[DCrake]

Just to let you know the demo I sent you handled OLE objects in Long Binary Format as opposed to Pictures. That is why you may have experienced problems. Will investigate further to see if there is a resolve.

 

[Paulsburbon]

I have a few days to fix this problem. I do appreciate that you respond to me so quickly to my posts.

 

[DCrake]

Is this going to be a one off exercise or an ongoing thing. If a one off and funds permit we could do it for you. Let me know.

Have been doing a bit of research and found out that of the 8 images you have in your table there was only one that could be sucessfully saved to file as a bitmap. Are these the real images in your database that you want to extract or are they examples?

(The one that was ok was ProductId 30)

If they are the real images then you may have issues with any type of automation.

 

[Max D]

You can try some programs for OLE objects extraction, like this one - http://www.lebans.com/oletodisk.htm

After you have saved your pictures as image files, there is a wide selection of ways. For example, you can try our AccessImagine component for convenient pictures handling.