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
hadronsAsked:
Who is Participating?
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.

Rey Obrero (Capricorn1)Commented:
you can use the "SaveToFile" method using VBA codes

see  How to: Work With Attachments In DAO
0
hadronsAuthor Commented:
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
0
Rey Obrero (Capricorn1)Commented:
so, what happened? did you get all the attachments saved to the folder?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

hadronsAuthor Commented:
Nothing happened
0
Rey Obrero (Capricorn1)Commented:
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
0
hadronsAuthor Commented:
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.
0
Rey Obrero (Capricorn1)Commented:
can you upload a copy of the db?
0
hadronsAuthor Commented:
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?
0
Rey Obrero (Capricorn1)Commented:
here in EE. click Attach File at the Left lower corner of the box where you type your comments.
0
hadronsAuthor Commented:
In see ... here it is
test.accdb
0
Rey Obrero (Capricorn1)Commented:
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
0
hadronsAuthor Commented:
Hi, the results are the same ... it extracts the first attachment only.
0
Rey Obrero (Capricorn1)Commented:
oops sorry.. use these codes


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")
     
    Do Until rstSource.EOF

                    Set rstAttachments = rstSource.Fields("AttachedPhoto").Value
                
                   rstAttachments.MoveFirst
                   While Not rstAttachments.EOF
                
                    rstAttachments.Fields("FileData").SaveToFile _
                                       CurrentProject.Path & "\"
                    '              "T:\Titles Database\COMMENTARY\Mike"
                      rstAttachments.MoveNext
                
                   Wend
                
        rstSource.MoveNext
    
    Loop
    
    
 End Sub

Open in new window

0

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
hadronsAuthor Commented:
Excellent ... worked exactly as desired
0
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
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.