在 Access VBA 中，如何将包含附件的字段从单个记录复制到另一个表? [英] In Access VBA, how do I copy fields which consist of Attachments from single record to another table?

问题描述

我尝试使用 SQL 但没有成功.然后我尝试了 DAO，其他领域似乎工作，但包含附件的列失败.以前有人这样做过吗?

I tried using SQL but with no success. I then tried DAO, the other fields seems to work but the column which holds attachments fails. Has someone done this before?

Private Sub copyfromtblA_Click()

Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset2
'Set db = CurrentDb()
Set rs1 = db.OpenRecordset("tblA")
Set rs2 = db.OpenRecordset("tblB")

With rs2
rs2.AddNew
rs2.Fields("ItemNo").Value = Me.ItemNo.Value
rs2.Fields("Quantity").Value = Me.Quantity.Value
rs2.Fields("itemName").Value = Me.itemName.Value
'This is were I get the error since this field contains images as attachments
rs2.Fields("ItemImage").Value = Me.itemImage.Value


rs2.Update
rs1.MoveNext

End With
rs2.Close

Form.Requery
Set rs2 = Nothing
rs1.Close
Set rs1 = Nothing

End Sub

推荐答案

是这样的:

Private Sub copyfromtblA_Click()
    Dim db As Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim rsAtt1 As DAO.Recordset2
    Dim rsAtt2 As DAO.Recordset2

    Set db = CurrentDb()
    Set rs2 = db.OpenRecordset("tblB")
    With Me.Recordset
        rs2.AddNew
        rs2.Fields("ItemNo").Value = !ItemNo.Value
        rs2.Fields("Quantity").Value = !Quantity.Value
        rs2.Fields("itemName").Value = !itemName.Value
        Set rsAtt1 = !ItemImage.Value
        Set rsAtt2 = rs2!ItemImage.Value
        With rsAtt1
            Do While Not .EOF
                rsAtt2.AddNew
                rsAtt2.Fields("FileData") = .Fields("FileData")
                rsAtt2.Fields("FileName") = .Fields("FileName")
                rsAtt2.Update
                .MoveNext
            Loop
        End With
        rs2.Update
    End With
    rs2.Close
    Set rs2 = Nothing
    rsAtt1.Close
    Set rsAtt1 = Nothing
    'I was getting an error here! removing the "rsAtt2.Close" solved the problem
    'rsAtt2.Close
    Set rsAtt2 = Nothing
End Sub

这篇关于在 Access VBA 中，如何将包含附件的字段从单个记录复制到另一个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！