How to extract images from the data type attachment

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

Select allOpen in new window

How are you executing this code?

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


 

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.

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?

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.

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?

>>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.


 

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.

GetOpenFile is part of code in the File Open/Save code on the following site:

http://www.mvps.org/access/api/api0001.htm

Man this would work beautiful if I could get the pictures to load back in the table. Not sure what i am doing wrong.

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?

I'll try to take a look tonight.

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.