Saving attachment to folder

[ino_mart]

All

I use Ms Access 2013 and I'm trying to save an attachment field into a file. I already tried some code found on the internet, but it fails.
The table is named tblTemplates. It has three fields:

• ID: autonumber primary key
• Filename: Text (filename of the attachment)
• File: Attachment (the actual attached file)

I use code at the bottom to save the attachment of a given ID.

Although the provided ID exists, the code does not work and fails on line
Set rsRecord = rsTemplate.Fields("filename").Values
This returns error "Run-Time error 424: Object Required"

If I change the line into
Set rsRecord = rsTemplate.Fields("file").Values
the code continues but it then fails at line
rsRecord.Fields("file").SaveToFile "C:\Documents and Settings\Username\My Documents"
with error "Run-time error 3265: Item not found in this collection".

If I change the line into


rsRecord.Fields("filename").SaveToFile "C:\Documents and Settings\Username\My Documents"
it results in "Run-time error 3259: Invalid field data type"

So what is the correct code?

Public Sub GetTemplateFile(ID As Integer)
Dim rsTemplate As Recordset2
Dim rsRecord As Recordset2
Dim strSQL As String


strSQL = "select * from tblTemplates where id=" & ID
Set rsTemplate = CurrentDb.OpenRecordset(strSQL)
If Not rsTemplate.BOF And Not rsTemplate.EOF Then
    ' Instantiate the child recordset.
    Set rsRecord = rsTemplate.Fields("filename").Value
    '  Save current attachment to disk in the "My Documents" folder.
    rsRecord.Fields("file").SaveToFile "C:\Documents and Settings\Username\My Documents"
 Else
    MsgBox ("Provided Template-ID does not exist")
End If
End Sub

Regards
Ino

[ItsMe]

I would pass the file attachment from the DAO recordset to a binary version using a Field2 object. Also, you might want to change the field name from File to something else, like MyFile. I don't know if file is a reserved word, but it sounds like it might be.

Dim rsTemplate As Recordset2
Dim rsRecord As Recordset2
Dim strSQL As String
Dim fldBinaryVers As DAO.Field2

strSQL = "select * from tblTemplates where id=" & ID

Set rsTemplate = CurrentDb.OpenRecordset(strSQL)

If Not rsTemplate.BOF And Not rsTemplate.EOF Then

    ' Instantiate the child recordset.
    Set rsRecord = rsTemplate.Fields("[File]").Value
    Set fldBinaryVers = rsRecord.Fields("FileData")

'    '  Save current attachment to disk in the "My Documents" folder.
'    rsRecord.Fields("file").SaveToFile "C:\Documents and Settings\Username\My Documents"
    fldBinaryVers.SaveToFile "C:\Documents and Settings\Username\My Documents\" & rsTemplate.Fields("filename").Value


 Else

    MsgBox ("Provided Template-ID does not exist")

End If

[ino_mart]

Thanks. It now works, although I had to make one small adjustment.

Public Sub GetTemplateFile(ID As Integer)
Dim rsTemplate As Recordset2
Dim rsRecord As Recordset2
Dim strSQL As String
Dim fldBinaryVers As DAO.Field2


strSQL = "select * from tblTemplates where id=" & ID


Set rsTemplate = CurrentDb.OpenRecordset(strSQL)


If Not rsTemplate.BOF And Not rsTemplate.EOF Then


    ' Instantiate the child recordset.
    Set rsRecord = rsTemplate.Fields("[Filedata]").Value
    Set fldBinaryVers = rsRecord.Fields("FileData")


   '  Save current attachment to disk in the "My Documents" folder.
    If Dir(CurrentProject.Path & "\" & rsTemplate.Fields("file").Value) <> "" Then
        Kill CurrentProject.Path & "\" & rsTemplate.Fields("file").Value
    End If
    fldBinaryVers.SaveToFile CurrentProject.Path & "\" & rsTemplate.Fields("file").Value
 Else


    MsgBox ("Provided Template-ID does not exist")


End If
End Sub

[ItsMe]

OK, cool ...