访问DAO将整个记录插入另一个记录集

Question

我想将所有的电话线从一个记录集移到另一个记录集，并想知道某个特定的方法是否可以做到这一点。这是我的代码。

Dim maxDate As Variant 

Dim db As DAO.Database 
Dim rs As DAO.Recordset 
Dim destin As DAO.Recordset 

maxDate = DMax("[Eff Date]", "400_CF_BREAK_LOG") 
Set db = CurrentDb 
Set rs = db.OpenRecordset("860_APPEND_DIFFERENCES") 'myTable is a MS-Access table created previously 
Set destin = db.OpenRecordset("400_CF_BREAK_LOG") 

'populate the table 
rs.MoveFirst 
Do While Not rs.EOF 
    If (rs![Eff Date] > maxDate) Then 
     destin. 
    Debug.Print (rs!myField) 'myField is a field name in table myTable 
rs.MoveNext 
Loop 

我陷入了目的地。 < ----在这里添加整个记录。

编辑。所选的答案虽然正确，但可能不会考虑表格之间不同数量的字段。我发现自己使用这个设置来添加三个不同的记录集。从Insert Complete RecordSet to another table in other database MS Access

maxDate = DMax("[Eff Date]", "400_CF_BREAK_LOG") 
Set db = CurrentDb 
Set rs = db.OpenRecordset("mytable") 'myTable is a MS-Access table created previously 

'populate the table 
rs.MoveFirst 
Do While Not rs.EOF 
    If (rs![Eff Date] > maxDate) Then 
     sqlinsert = "INSERT INTO 400_CF_BREAK_LOG (Eff Date, PrimarySecurity ID Number, CUSIP(Aladdin ID), IsrName, Asset Type, Metlife Port Code, Business Unit, Principal Difference, Total PAM Principal, Total Aladdin Principal,Income Difference, Total PAM Interest,Total Aladdin Interest,Total CF Difference,Total PAM CF,PAM Coupon)" & _ 
     " VALUES ('" & rs("Eff Date") & "', '" & rs("PrimarySecurity ID Number") & "', '" & rs("CUSIP(Aladdin ID)") & "', '" & rs("IsrName") & "', '" & rs("Asset Type") & "', '" & rs("Metlife Port Code") & "', '" & rs("Business Unit") & "', '" & rs("Principal Difference") & "', '" & rs("Total PAM Principal") & "', '" & rs("Total Aladdin Principal") & "', & '" & rs("Income Difference") & "', '" & rs("Total PAM Interest") & "', '" & rs("Total Aladdin Interest") & "', '" & rs("Total CF Difference") & "', '" & rs("Total PAM CF") & "', '" & rs("PAM Coupon") & "')" 
     DoCmd.RunSQL (sqlinsert) 
    rs.MoveNext 
Loop 

Answer 1

拍摄可以修改此你的需要（这里源和目标是相同的表）：

Public Sub CopyRecords() 

    Dim rstSource As DAO.Recordset 
    Dim rstInsert As DAO.Recordset 
    Dim fld   As DAO.Field 
    Dim strSQL  As String 
    Dim lngLoop  As Long 
    Dim lngCount As Long 

    strSQL = "SELECT * FROM tblStatus WHERE Location = '" & _ 
       "DEFx" & "' Order by Total" 

    Set rstInsert = CurrentDb.OpenRecordset(strSQL) 
    Set rstSource = rstInsert.Clone 
    With rstSource 
    lngCount = .RecordCount 
    For lngLoop = 1 To lngCount 
     With rstInsert 
     .AddNew 
      For Each fld In rstSource.Fields 
      With fld 
       If .Attributes And dbAutoIncrField Then 
       ' Skip Autonumber or GUID field. 
       ElseIf .Name = "Total" Then 
       ' Insert default job code. 
'     datNow = Now 
       rstInsert.Fields(.Name).Value = 0 
       ElseIf .Name = "PROCESSED_IND" Then 
       rstInsert.Fields(.Name).Value = vbNullString 
       Else 
       ' Copy field content. 
       rstInsert.Fields(.Name).Value = .Value 
       End If 
      End With 
      Next 
      ' Insert fields not existing in source table 
      rstInsert!SomeField.Value = SomeValue 
      rstInsert!SomeOtherField.Value = SomeOtherValue 
      rstInsert!YetAField.Value = ThirdValue 
     .Update 
     End With 
     .MoveNext 
    Next 
    rstInsert.Close 
    .Close 
    End With 

    Set rstInsert = Nothing 
    Set rstSource = Nothing 

End Sub