Link to home
Start Free TrialLog in
Avatar of hadrons
hadrons

asked on

How to extract jpeg attachments from an Access 2007 file

I have an Access 2007 file with a table of about 300 jpgs imported into it as an attachment and I want to know how can I extract all these jpegs without having to do so one at a time; thanks
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

you can use the "SaveToFile" method using VBA codes

see  How to: Work With Attachments In DAO
Avatar of hadrons
hadrons

ASKER

I've never worked with VBA and when I tried this ... the table name is tBNExport and the column with the attachments is AttachedPhoto:

Sub InsertAttachments()
    Dim dbs As DAO.Database
    Dim rstSource As DAO.Recordset
    Dim rstAttachments As DAO.Recordset

    Set dbs = CurrentDb
    Set rstSource = dbs.OpenRecordset("tBNExport")
    Set rstAttachments = rstSource.Fields("AttachedPhoto").Value
 
   While Not rstAttachments.EOF
 
    rstAttachments.Fields("AttachedPhoto").SaveToFile _
                  "T:\My Data\Mike"
      rstAttachments.MoveNext
 
   Wend
 
 
End Sub
so, what happened? did you get all the attachments saved to the folder?
Avatar of hadrons

ASKER

Nothing happened
try modifying the codes like this

Sub InsertAttachments()
     Dim dbs As DAO.Database
     Dim rstSource As DAO.Recordset
     Dim rstAttachments As DAO.Recordset

     Set dbs = CurrentDb
     Set rstSource = dbs.OpenRecordset("tBNExport")
     Set rstAttachments = rstSource.Fields("AttachedPhoto").Value
 
    While Not rstAttachments.EOF
 
     rstAttachments.Fields("FileData").SaveToFile _
                   "T:\My Data\Mike"
       rstAttachments.MoveNext
 
    Wend
 
 
 End Sub
Avatar of hadrons

ASKER

A huge improvement ... it runs perfectly except that it yanks out the first file and stops there and the file has 300+ of these attachments.  I ran this directly from the module design view in Access.
can you upload a copy of the db?
Avatar of hadrons

ASKER

I can upload a sample copy with two records (its the one I'm working with - its still has multiple record and if the function works for two it should work for more) ... do you have a place where I can upload it?
here in EE. click Attach File at the Left lower corner of the box where you type your comments.
Avatar of hadrons

ASKER

In see ... here it is
test.accdb
test this.. after running the codes from the modules ( i revised the path )

look in the locations (folder) where you will place the test_revised.accdb i uploaded.
test-revised.accdb
Avatar of hadrons

ASKER

Hi, the results are the same ... it extracts the first attachment only.
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of hadrons

ASKER

Excellent ... worked exactly as desired