Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 943
  • Last Modified:

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
1
hadrons
Asked:
hadrons
  • 7
  • 7
1 Solution
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now