Access 2007 Attachments and Loops (1 Viewer)

BrokenBiker · Jun 19, 2010

Many people are interested in managing attachments in Access 2007, but there isn't much info out there. I was able to find a little something on the subject.

However, the line of code for exporting (.SaveToFile) will only 'Save As' one attachment in the one record for the form. I added a little basic 'Loop' to have it actually 'SaveAs' x amount of attachments. It works great...on the individual record.

Code:

On Error GoTo Err_SaveImage

Dim db As DAO.Database
Dim rsParent As DAO.Recordset2
Dim rsChild As DAO.Recordset2

Set db = CurrentDb
Set rsParent = Me.Recordset

rsParent.OpenRecordset

Set rsChild = rsParent.Fields("Attachment_CrsList").Value

    With rsChild
    Do Until .EOF
    .MoveFirst
    
    rsChild.OpenRecordset
    rsChild.Fields("FileData").SaveToFile ("C:\Users\Broken.Biker\Desktop\Test")
    
    rsChild.Delete
    Me.Refresh
    
    .MoveNext
    
    Loop
    .Close
    MsgBox "Record cw"
    End With

Exit_SaveImage:
Set rsChild = Nothing
Set rsParent = Nothing
Exit Sub

Err_SaveImage:
If Err = 3839 Then
    MsgBox ("File Already Exists in the Directory!")
    Resume Next
Else
    MsgBox "Some Other Error occured!", Err.Number, Err.Description
    Resume Exit_SaveImage
End If

I tried to apply the same logic/loop to have it loop through the entire table the form is based on, but I get "Some Other Error occured!"...and it doesn't proceed to the next record.

Code:

On Error GoTo Err_SaveImage

Dim db As DAO.Database
Dim rsParent As DAO.Recordset2
Dim rsChild As DAO.Recordset2

Set db = CurrentDb
Set rsParent = Me.Recordset

rsParent.OpenRecordset

Set rsChild = rsParent.Fields("Attachment_CrsList").Value

With rsParent
Do Until .EOF
.MoveFirst

    With rsChild
    Do Until .EOF
    .MoveFirst
    
    rsChild.OpenRecordset
    rsChild.Fields("FileData").SaveToFile ("C:\Users\Broken.Biker\Desktop\Test")
    
    rsChild.Delete
    Me.Refresh
    
    .MoveNext
    
    Loop
    .Close
    MsgBox "Record cw"
    End With

.MoveNext
Loop
.Close
MsgBox "Recordset done, son!"
End With

Exit_SaveImage:
Set rsChild = Nothing
Set rsParent = Nothing
Exit Sub

Err_SaveImage:
If Err = 3839 Then
    MsgBox ("File Already Exists in the Directory!")
    Resume Next
Else
    MsgBox "Some Other Error occured!", Err.Number, Err.Description
    Resume Exit_SaveImage
End If

So, is there a way to have it loop through a record and through the entire table of records?

Any help is greatly appreciated.
-BB

MarkK · Jun 19, 2010

This is an endless loop ...

Code:

with recordset
  do while not .eof
    .movefirst
    '
  loop
end with

BrokenBiker · Jun 20, 2010

Lagbolt,
I don't think I quite understand your post. Granted, I don't have a lot of experience w/recordsets in VBA, but I thought that basically the With-MoveFirst-MoveNext-Loop-Close-End With format would work.

Code:

With rsParent
Do Until .EOF
.MoveFirst

'Insert do-stuff code here

.MoveNext
Loop
.Close
End With

I also found a little tid-bit on 2007, plus some extra notes concerning VBA and recordsets.

Access 2007 introduced the possibility that a field in a recordset may itself be a recordset

So, the original code to SaveAs attachments in a table basically treats an individual record/field as a recordset. So I had that code looped for multiple attachments.

I'm wondering if maybe I'm missing something along the lines of Items 5 & 6?

MarkK · Jun 21, 2010

If the first line of your loop moves to the first record then .eof will never be true and the loop will never terminate.

Code:

with recordset
[COLOR="Green"]  '.eof will never be true[/COLOR]
  do while not .eof
[COLOR="Green"]    'move to first record[/COLOR]
    .movefirst
[COLOR="Green"]    'process data
      ' ...[/COLOR]
[COLOR="Green"]    'move to next record[/COLOR]
    .movenext
  loop [COLOR="Green"]'executes first line of loop again, which is .movefirst[/COLOR]
end with

This is a problem with both of the code blocks you posted.

BrokenBiker · Jun 22, 2010

A little closer....Here's what I have now.

Code:

Dim db As DAO.Database
Dim rsParent As DAO.Recordset2
Dim rsChild As DAO.Recordset2

Set db = CurrentDb
Set rsParent = Me.Recordset

rsParent.OpenRecordset

With rsParent
.MoveFirst
Do While Not .EOF

    Set rsChild = rsParent.Fields("Attachment_CrsList").Value
    
    With rsChild
    .MoveFirst
    Do While Not .EOF
    
    rsChild.OpenRecordset
    rsChild.Fields("FileData").SaveToFile("C:\Users\Adam.Brunkow\Desktop\Test")
    
    rsChild.Delete
    Me.Refresh
    
    .MoveNext
    
    Loop
    .Close
    MsgBox "Record cw"
    End With

.MoveNext
Loop
.Close
MsgBox "Recordset cw"
End With

However, w/records that have no attachments, an error occurs and I get the message "Some Other Error occured!" The good news is that after saving the files in the Test folder it proceeds to the next record. But, if the next record doesn't have any attachments, the "other error" occurs. I'm sure there's a way of catching that and resuming the process.

Inputs appreciated. I'll plug away at it tomorrow.

BrokenBiker · Jun 23, 2010

Whoo-hoo!!! It's fixed!

Well...it's mostly fixed. The only issue now is that after the recordset is completed, the recordset is set to 'Nothing' which makes the fields all ????

I can work around it w/out any problems for what I'll be using it for, but is there a way to re-establish the form properties back to the original table while at the same setting the recordsets to nothing?

Tags: 2007, Attachments, Loop, Recordset, Export, SaveAs, All Records

pedie · Aug 4, 2011

Hi, 'm wanting copy the attachment frm one table to another...how can accomplish that...

Please help if anyone if possible...

Thanks in advance

Access 2007 Attachments and Loops (1 Viewer)

BrokenBiker

ManicMechanic

MarkK

bit cruncher

BrokenBiker

ManicMechanic

MarkK

bit cruncher

BrokenBiker

ManicMechanic

Attachments

BrokenBiker

ManicMechanic

Attachments

pedie

Registered User.

Similar threads

Users who are viewing this thread