Problem with append query for attachment field

[ahmadrezaahmad]

I have 1 "main" access file and "Portable".
in the form of main I creat buttom to open and apped the table of other access file-portable.accdb- to the main table!
I have a problem, it is that when I add field content of attachment to the main table I can not use "Append" buttom !
attach:
1-befor adding attachmend field=ok!
2-after adding attachment field=ERRROR!!!!
Please help me!
Thank you

[June7]

Cannot append attachment field with query. Requires VBA code to save the file(s) to external folder then save external files back into new record. Review http://blogs.office.com/b/microsoft-...cess-2007.aspx

Embedding files in table chews up file limit. Access has a 2gb size limit.

[ahmadrezaahmad]

Requires VBA code to save the file(s) to external folder then save external files back into new record. Review http://blogs.office.com/b/microsoft-...cess-2007.aspx

this code for one file and I had seen this code!
but my problem is appendin from one db to another db!
for example in my file ,main db is (main.accdb) and other persons fill forms in other db (portable.acccdb),I want to append forms that recieved from persons in many file as (portable.accdb) to one main file=main.accdb!
for this sitution what your idea???(pls See attachmentS)
thanks!

[June7]

Adapt code. The process is basically the same. Open query of source table in 'portable' db. Open query of destination table in 'main' db. Save attachment from source to a folder. Save file from folder into destination attachment.

Data from the four PK fields will have to be saved in a separate SQL action then run code to move the attachment data.

The [to] field is not a unique value and therefore appears not useful as criteria in the query. Seems would need all four fields of the compound PK. Don't understand what the data represents. The [time] field is number type set as Long Integer but input mask is yyyy/mm/ddd. The date displays like 1392/04/04 which is surely not a valid date. Date values should be stored in a date/time field type. Should not use [from], [to], [time] as field names. One or all of them cause the INSERT sql to fail if the [] are omitted. Also, describtion is a misspelling of description.

Here is code distilled from the referenced links that works for me.

Sub btnAppend_Click()
Dim strExtract As String
Dim dbs As DAO.Database
Dim rstSource As DAO.Recordset
Dim rstSourceChild As DAO.Recordset
Dim rstDest As DAO.Recordset
Dim rstDestChild As DAO.Recordset
Dim strFilePath As String
strExtract = "C:\Temp\Portable.accdb"
CurrentDb.Execute "INSERT INTO asli([from], [to], [describtion], [time]) SELECT [from], [to], [describtion], [time] FROM [" & strExtract & "].asli;"
Set dbs = OpenDatabase("C:\Temp\Portable.accdb")
Set rstSource = dbs.OpenRecordset("SELECT * FROM asli;", dbOpenDynaset)
While Not rstSource.EOF
    Set rstSourceChild = rstSource.Fields("attach").Value
    If Not rstSourceChild.EOF Then
        Set rstDest = CurrentDb.OpenRecordset("SELECT * FROM asli WHERE [from]='" & rstSource![from] & _
            "' AND [to]='" & rstSource![to] & _
            "' AND describtion='" & rstSource!describtion & _
            "' AND [time]=" & rstSource![Time], dbOpenDynaset)
        Set rstDestChild = rstDest.Fields("attach").Value
        rstDest.Edit
        While Not rstSourceChild.EOF
            'save attachment from Portable to folder
            strFilePath = "C:\Temp\" & rstSourceChild.Fields("FileName")
            If Dir(strFilePath) <> "" Then VBA.Kill strFilePath
            rstSourceChild.Fields("FileData").SaveToFile strFilePath
            'save attachment from folder to Main
            rstDestChild.AddNew
            rstDestChild.Fields("FileData").LoadFromFile strFilePath ' store the file's contents in the new row.
            rstDestChild.Update ' commit the new row.
            rstSourceChild.MoveNext
        Wend
        rstSourceChild.Close
        rstDestChild.Close
        rstDest.Update
        rstDest.Close
    End If
    rstSource.MoveNext
Wend
End Sub

[ahmadrezaahmad]

thanks a lot..
I try to adapt your code to my file but I have error..
can yo attach your "ok" file to me?
I like my code is general like "befor adding attaments field" file and not depend on file folder like your coed:

strExtract = "c:\Temp\Portable.accdb"

can you help me with attaching true file

tnx again!

[June7]

What is the error?

All I did was replace the button code and run it. Did not have to change anything else. The following will allow user to select source file path with the file dialog. Get this to work as is before trying more customization.

Dim dbs As DAO.Database
Dim rstSource As DAO.Recordset
Dim rstSourceChild As DAO.Recordset
Dim rstDest As DAO.Recordset
Dim rstDestChild As DAO.Recordset
Dim strSql As String, fd As FileDialog
Dim strFilePath As String, strExtract As String
Set fd = FileDialog(msoFileDialogFilePicker)
With fd
    .AllowMultiSelect = False
    .Filters.Add "Access Files", "*.mdb;*.accdb", 1
    .InitialFileName = CurrentProject.Path
    If .Show Then strExtract = .SelectedItems(1)
End With
If strExtract <> "" Then
    If MsgBox("REWRITE?", vbYesNo + vbQuestion) = vbYes Then
        CurrentDb.Execute "DELETE FROM asli"
    End If
    
    CurrentDb.Execute "INSERT INTO asli([from], [to], describtion, [time]) " & _
                        "SELECT [from], [to], describtion, [time] FROM " & strExtract & ".asli"
    
    Set dbs = OpenDatabase(strExtract)
    Set rstSource = dbs.OpenRecordset("SELECT * FROM asli;", dbOpenDynaset)
    While Not rstSource.EOF
        Set rstSourceChild = rstSource.Fields("attach").Value
        If Not rstSourceChild.EOF Then
            Set rstDest = CurrentDb.OpenRecordset("SELECT * FROM asli WHERE from='" & rstSource![from] & _
                "' AND to='" & rstSource!to & _
                "' AND describtion='" & rstSource!describtion & _
                "' AND time=" & rstSource!Time, dbOpenDynaset)
            Set rstDestChild = rstDest.Fields("attach").Value
            rstDest.Edit
            While Not rstSourceChild.EOF
                'save attachment from Portable to folder
                strFilePath = "C:\Temp\" & rstSourceChild.Fields("FileName")
                If Dir(strFilePath) <> "" Then VBA.Kill strFilePath
                rstSourceChild.Fields("FileData").SaveToFile strFilePath
                'save attachment from folder to Main
                rstDestChild.AddNew
                rstDestChild.Fields("FileData").LoadFromFile strFilePath ' store the file's contents in the new row.
                rstDestChild.Update ' commit the new row.
                rstSourceChild.MoveNext
            Wend
            rstSourceChild.Close
            rstDestChild.Close
            rstDest.Update
            rstDest.Close
        End If
        rstSource.MoveNext
    Wend
End If

[ahmadrezaahmad]

hi
unfrotunatly your 2nd code fail too!!
I attached 2 Error Screen shots!
pls attach your "OK" access file!!!
I import my module to chechk it!!
tnx

[June7]

This is weird. I wrote and tested that code in Access 2007 which worked. I just tested in Access 2010 and it doesn't work. Don't understand why.

[ahmadrezaahmad]

please upload your accdb file....
please....

[June7]

Attaching db with that code won't do you any good. See my previous post. The code isn't working for Access 2010/2013.

The only alternative I can see is to set a link to the asli table in Portable. In my test I call the link asliPortable. Then this code works to copy records from asliPortable to asli. Use the Import/Export wizard to set the link then copy/paste this code into the button Click event.

Dim rstSource As DAO.Recordset
Dim rstSourceChild As DAO.Recordset
Dim rstDest As DAO.Recordset
Dim rstDestChild As DAO.Recordset
Dim strFilePath As String, strExtract As String
If MsgBox("REWRITE?", vbYesNo + vbQuestion) = vbYes Then
    CurrentDb.Execute "DELETE FROM asli"
End If
CurrentDb.Execute "INSERT INTO asli([from], [to], describtion, [time]) " & _
                    "SELECT [from], [to], describtion, [time] FROM asliPortable"
Set rstSource = CurrentDb.OpenRecordset("SELECT * FROM asliPortable;", dbOpenDynaset)
While Not rstSource.EOF
    Set rstSourceChild = rstSource.Fields("attach").Value
    If Not rstSourceChild.EOF Then
        Set rstDest = CurrentDb.OpenRecordset("SELECT * FROM asli WHERE from='" & rstSource![from] & _
            "' AND to='" & rstSource!to & _
            "' AND describtion='" & rstSource!describtion & _
            "' AND time=" & rstSource!Time, dbOpenDynaset)
        Set rstDestChild = rstDest.Fields("attach").Value
        rstDest.Edit
        While Not rstSourceChild.EOF
            'save attachment from Portable to folder
            strFilePath = "C:\Users\June\Forums\" & rstSourceChild.Fields("FileName")
            If Dir(strFilePath) <> "" Then VBA.Kill strFilePath
            rstSourceChild.Fields("FileData").SaveToFile strFilePath
            'save attachment from folder to Main
            rstDestChild.AddNew
            rstDestChild.Fields("FileData").LoadFromFile strFilePath ' store the file's contents in the new row.
            rstDestChild.Update ' commit the new row.
            rstSourceChild.MoveNext
            Kill strFilePath
        Wend
        rstSourceChild.Close
        rstDestChild.Close
        rstDest.Update
        rstDest.Close
    End If
    rstSource.MoveNext
Wend