How to extract images from the data type attachment

Is it possible to extract the images or pictures that are in the attachement data type to a folder and give it a name?
chovis78Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
Here is some code I cludged together from several sources.  I use this to extract some simple bitmaps that I use as images in a treeview control.
Public Sub ExtractAttachment(TableName As String, FieldName As String, Optional Criteria As Variant = Null)

    'Opens a recordset base on TableName, and extracts all attachments from field: FieldName
    'If no criteria string is provided, loops through all records in the table and extracts all of the attachments
    
    Dim rsMain As DAO.Recordset, rsAtt As DAO.Recordset
    Dim strSQL As String
    Dim strPath As String
    
    On Error GoTo ProcError
    
    strSQL = "SELECT * FROM [" & TableName & "]" & (" WHERE " + Criteria)
    strSQL = Replace(Replace(strSQL, "[[", "["), "]]", "]")
    Set rsMain = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
    
    'Select the folder to extract the files to
    strPath = CurrentProject.Path & "\"
    
    'Loop through the records
    Do While Not rsMain.EOF
        
        Set rsAtt = rsMain.Fields(FieldName).Value
        While Not rsAtt.EOF
            rsAtt.Fields("FileData").SaveToFile strPath
            rsAtt.MoveNext
        Wend
        rsMain.MoveNext
    Loop
    
ProcExit:
    If Not rsAtt Is Nothing Then
        rsAtt.Close
        Set rsAtt = Nothing
    End If
    If Not rsMain Is Nothing Then
        rsMain.Close
        Set rsMain = Nothing
    End If
    Exit Sub
ProcError:
    If Err.Number = 3420 Then
        Resume Next
    ElseIf Err.Number = 3820 Then
        MsgBox "Selected file already exists in this record!"
        Resume Next
    ElseIf Err.Number = 3839 Then
        Resume Next
    Else
        Debug.Print Err.Number, Err.Description, "LoadAttachment"
        Resume ProcExit
    End If
    
End Sub

Open in new window

chovis78Author Commented:
How are you executing this code?
Dale FyeOwner, Developing Solutions LLCCommented:
It's a subroutine.

When I open my splash form, I check to see whether the images already exist in the folder where the front-end of the application is operating from.  If not, I call the subroutine and pass it the name of the table where the attachments are loaded and the name of the field which contains the attachments.  

In the sample (attached), I created a new table "zz_Test_Attachment" which contains a field (MyImage) which is of datatype = attachment.  I then added a couple of jpg files to it.  If you call the subroutine from your immediate window, it will extract those two jpg files into whatever directory you download the accdb file to.

call ExtractAttachment("zz_Test_Attachment", "MyImage")


Attachments.accdb

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jeffrey CoachmanMIS LiasonCommented:
This is the reason why many developers don't store images IN the DB, they simply link to them.

At first, it seems cool when he images are in the DB, but eventually someone wants to "save"  the image to disk.

If the image was linked, this would not be an issue because the image was never in the database to begin with...
Thus it never has to be extracted

<Climbing down from Soapbox...>
;-)


"extract the images or pictures that are in the attachment data type to a folder and give it a name?"
Give it a name when?
The extracted file will already be named what the original file was named.
Do you want to "RE"-Name it?
Do you want it to stop and ask you for a name for every file?
...or do you want some kind of incremental naming system?

At first you say: "PictureS", (Plural), then you say: "Give IT a name". (Singular)

So are you trying to extract multiple files, or just one specific file?


FWIW, I also found that same code nugget and adapted it for my needs.
fyed's looks a bit more "refined" (being a function and all...), so I'll not embarrass myself by posting mine here.
:-(

JeffCoachman


 
Dale FyeOwner, Developing Solutions LLCCommented:
As you will note, the subroutine also accepts a "Criteria" string, like you would find in DLOOKUP, DMAX, DMIN, ...
so if you only want to extract an attachment from a single record, you can do so based on some criteria string.

As Jeff noted, I don't generally include attachments in my databases either, also preferring to store the path and filename, but when I distribute a new front end and need to send some files to go with it, I use this technique.  Then I delete all the records from this table and configure the application to compact on close.
chovis78Author Commented:
Ok that works. Thanks, but is there a way to rename the files to say the id that goes with all the picutures. The reason is so I can match them up easily to the records when I link them back properly. Yes I know linking is the way to go but this project started off small and somehow grew bigger than I thought it would. Also what are your thoughts on having pictures within a SQL table since 2008 has no size limit on the database?
Dale FyeOwner, Developing Solutions LLCCommented:
I'm not actually sure how to get the filename of the attachment but I'm sure there is a property associated with it that you can retrieve.

In the absence of that, I would imagine that you could extract the file to a folder that contains no other files, then use the DIR() function to get the name of the file, then use the VBA Name statement to change the files name from the original text to the ID number of the record containing the attachment.  If I were going to do this, I would probably add a field to the table to store the original name of the attachment, so that I could retrieve it with its original name at some point in the future.
chovis78Author Commented:
Is that all in that sample database you shared or is that something else?

What is that LoadAttachment code you have, is that to attach them back into the table?
Dale FyeOwner, Developing Solutions LLCCommented:
>>Is that all in that sample database you shared or is that something else?
No, the sample I posted does not have anything that would do the renaming of the file based on an ID field.  If I were going to do that, I would probably do it as part of the upload process.  I would select the file using the FileOpenSave API, then copy it to a new name that looks like the ID value of the record I'm about to attach it to.  I would then attach it to the record and store the original file name to a field in that record as well.

>>What is that LoadAttachment code you have, is that to attach them back into the table?
Yes, that is for loading an attachment, although I have to admit that I have not used it in quite a while.


 
chovis78Author Commented:
I think your LoadAttachment code is missing the function GetOpenFile not sure if you still have that? Was going to try and see if that will work.
Dale FyeOwner, Developing Solutions LLCCommented:
GetOpenFile is part of code in the File Open/Save code on the following site:

http://www.mvps.org/access/api/api0001.htm
chovis78Author Commented:
Man this would work beautiful if I could get the pictures to load back in the table. Not sure what i am doing wrong.
chovis78Author Commented:
Ok i got it to extract like i want and can load, but it only loads one at a time and adds a new record. Do you know of a way to add all of them and say match the name of the file to a record and insert the picture in that specific record?
Dale FyeOwner, Developing Solutions LLCCommented:
I'll try to take a look tonight.
mlmccCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.