Exporting Attachments per record

[justinwright]

I know this is an odd question, but I couldn't find anything to answer it.

I have a database right now that has the 2007 Attachment field in it. Thanks to a few of you guys helping me tons, I've started migrating it to a custom backend that stores files externally (not using the attachment field so virtually removing the DB limitation concerns I had).



It's a split back end database that has around 1800 records.

My question is this:

Is there any way whatsoever to extract those attachment files from the Attachment field to an external source? I'd rather not have to go through an individually back up 2 - 3 files per record for 1800 records... I think I might go insane

Optimistically, this is how it would work:

There is a field called NUMBERONE that is the main entry for the DB (all of it hinges on that; it will never be duplicated).

Each NUMBERONE has an attachment field (2007) that contains anywhere from 0 to 4 attachments.

I would like to be able to run something that will make a folder NUMBERONE and put all the attachments in it.

So lets say for example I have a record 123890 with attachments test.txt, test.pdf, and testing.doc. I'd like it to mvoe to a folder C:\Extracts\ called 123890, and inside C:\Extracts\123890\ would be the 3 individual attachments, able to be viewed externally (not related to or stored in the Access DB at all).

I have a .bat file that will run and create all the folders for me (painstakingly pasted each one to make the directory T_T).

Any help is appreciated, PLEASE let there be some solution similar to this! T_T

[June7]

Check out this article: http://blogs.office.com/b/microsoft-...cess-2007.aspx

[justinwright]

'-------------------------------------------------------------------------
' Sub/Func : SaveAttachments
' Purpose  : Saves the attachments at the current row of the open Recordset
' Arguments: rstCurrent - The recordset open at the current row to save
'          : strFieldName - The name of the attachment field
'          : strOutputDir - The folder to put the files in (e.g. "C:\Foo\")
' -------------------------------------------------------------------------
Sub SaveAttachments(ByRef rstCurrent As DAO.Recordset, ByVal strFieldName As String, ByVal strOutputDir As String)
    Const CALLER = "SaveAttachments"
    On Error GoTo SaveAttachments_ErrorHandler

    Dim rstChild As DAO.Recordset2
    Dim fldAttach As DAO.Field2
    Dim strFilePath As String

    If Right(strOutputDir, 1) <> "\" Then strOutputDir = strOutputDir & "\"
    Set rstChild = rstCurrent.Fields(strFieldName).Value ' The .Value for a complex field returns the underlying Recordset.
    While Not rstChild.EOF ' Loop through all of the attached files in the child Recordset.
        strFilePath = strOutputDir & rstChild.Fields(m_strFieldFileName).Value 'Append the name of the attached file to output directory.
        If Dir(strFilePath) <> "" Then ' The file already exists--delete it first.
            VBA.SetAttr strFilePath, vbNormal ' Remove any flags (e.g. read-only) that would block the kill command.
            VBA.Kill strFilePath ' Delete the file.
        End If
        Set fldAttach = rstChild.Fields(m_strFieldFileData) ' The binary data of the file.
        fldAttach.SaveToFile strFilePath
        rstChild.MoveNext ' Go to the next row in the child Recordset to get the next attached file.
    Wend
    rstChild.Close ' cleanup

    Exit Sub
SaveAttachments_ErrorHandler:
    Debug.Print "Error # " & Err.Number & " in " & CALLER & " : " & Err.Description
    MsgBox Err.Description, VbMsgBoxStyle.vbCritical, "Error # " & Err.Number & " in " & CALLER
    Debug.Assert False ' always stop here when debugging
    Resume Next
End Sub 'SaveAttachments

I assume that I put the code in a button or something? How would I execute it otherwise? I see the arguments for the function, so I was thinking adding text boxes on the form with the info in them (with the proper names) and then putting the code for the above function on a button.

The only other question I had is that... well.. how will I know what extracted attachment corresponds to what record? Since with the test code

    If Dir("C:\Foo\", vbDirectory) = "" Then MkDir "C:\Foo"
    SaveAttachments rst, strField, "C:\Foo\"
    rst.Close

It seems like it would just lump them all into one directory (and the file names themselves are no indication of the NUMBERONE field unfortunately.

Also does this go through each attachment? It seems like it only goes through the record specified and extracts the attachments.


Thank you very much for that, that looks like just what I needed. I'll try to use it now and post back if I blow something up by accident, otherwise I'll post and mark thread as solved if it works. Thank you again!

[justinwright]

When I try what I proposed with the form's source bound to the table in question, it stops me on load and says

The search key was not found in any record

When I set it to an unbound form and try to run the code from the button I get

Run-Time Error 3709: The search key was not found in any record

And when I debug it points to:

    Set rst = dbs.OpenRecordset(strTable)

Out of the following test code:

Private Sub Button1_Click()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Const strTable = "TrackingTable"
    Const strField = "Files" ' Attachment field in Table1

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strTable)

    If dir("C:\Extract\", vbDirectory) = "" Then MkDir "C:\Extract\"
    SaveAttachments rst, strField, "C:\Extract\"
    rst.Close


End Sub

[June7]

First of all, only found the code, not used. So I have no idea what the pitfalls are but you seem to have hit a major one. I don't know what that error message means. The referenced article doesn't indicate to select References in the VBA editor but I expect would need Microsoft DAO 3.6 Object Library. Do you have this selected (if it weren't, think would have gotten different error)? Does the code compile?

Don't know if code can handle multiple attachments for each record.

Did you declare the constants in the module header?

If you get past this issue and code works, then you could modify the loop that reads the records to create a subfolder with a name that would identify the source record and install that record's attachments there. Here is one article on this (again, found not tested) http://www.access-2003.com/content/m...ory-access-vba

Then could have another procedure that goes through these subfolders, renames the files to add some identifiying prefix and copy them into one common folder if you want.

Since the code uses recordsets, should not need a bound form.

UPDATE: Did a test of the code from site. It worked, even without setting the DAO reference I thought would be needed. My attachment field had only one item. Suggest you test code on a table where only one item in attachment field.

[justinwright]

It worked for me on one attachment-per-field tables as well, but I was under a deadline so I had to do it manually

Thank you very much for the help though