您如何将整个记录从一个表复制到另一个表，包括归档的附件? [英] How do you copy the entire record from one table to another including attachment filed?

问题描述

我有两个表，tb1和tb2.我想将整个记录从tab1复制到tbl2.这些表包含附件字段，因此INSERT语句不合适.我当前的方法使用DAO，但仅复制第一条记录.请查看代码:

I have two tables, tb1 and tb2. I would like to copy the entire record from tab1 to tbl2. The tables contain attachment fields so INSERT statement is not suitable. My current approach uses DAO but its only copying the first record. Please see code:

Private Sub InsertRecord_Click()

Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rsAttachment1 As DAO.Recordset2
Dim rsAttachment2 As DAO.Recordset2

Set rs1 = CurrentDb.OpenRecordset("tbl1")
Set rs2 = CurrentDb.OpenRecordset("tbl2")


With rs1
rs2.AddNew
rs2.Fields("ItemNo").Value = rs1.Fields("ItemNo").Value
rs2.Fields("Location").Value = rs1.Fields("Location").Value
rs2.Fields("Owner").Value = rs1.Fields("Owner").Value
rs2.Fields("DateSent").Value = DateTime.Now


    Set rsAttachment1 = rs1.Fields("ItemImage").Value
    Set rsAttachment2 = rs2.Fields("ItemImage").Value

    With rsAttachment1
        Do While Not .EOF
            rsAttachment2.AddNew
            rsAttachment2.Fields("FileData") = .Fields("FileData")
            rsAttachment2.Fields("FileName") = .Fields("FileName")

            rsAttachment2.Update
            rsAttachment1.MoveNext

        Loop
    End With
   rs2.Update
  .MoveNext
   End With
   rs2.Close
   Set rs2 = Nothing
  'rsAttachment1.Close
  Set rsAttachment1 = Nothing
  Set rsAttachment2 = Nothing

  End Sub

也欢迎任何其他更好的方法.

Any other better approach is also welcome.

推荐答案

使用循环:

While Not rs1.EOF

    With rs1
        rs2.AddNew
        rs2.Fields("ItemNo").Value = rs1.Fields("ItemNo").Value
        rs2.Fields("Location").Value = rs1.Fields("Location").Value
        rs2.Fields("Owner").Value = rs1.Fields("Owner").Value
        rs2.Fields("DateSent").Value = DateTime.Now   

        Set rsAttachment1 = rs1.Fields("ItemImage").Value
        Set rsAttachment2 = rs2.Fields("ItemImage").Value

        With rsAttachment1
            Do While Not .EOF
                rsAttachment2.AddNew
                rsAttachment2.Fields("FileData") = .Fields("FileData")
                rsAttachment2.Fields("FileName") = .Fields("FileName")        
                rsAttachment2.Update
                rsAttachment1.MoveNext        
            Loop
        End With
        rs2.Update
        .MoveNext
    End With

    rs1.MoveNext
Wend

这篇关于您如何将整个记录从一个表复制到另一个表，包括归档的附件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！