Access VBA:如何将当前索引复制到其他表中

Question

我正在尝试编写此程序：

     Function funktion()
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim rt As DAO.Recordset
        Set db = CurrentDb
        Set rs = db.OpenRecordset("All")
Do While Not rs.EOF

Set rk = db.OpenRecordset("Archive")
'here I want to copy(append) the current index(of Table"All") into the next free
 index (of table "archive") 

Do something

    rs.MoveNext
Loop

我的程序运行得很好，只需要将"All"中的当前行附加到表"Archive"中的下一个空闲行中。

感谢您的帮助

Answer 1

这里有一个示例，说明如何使用ADO而不是DAO访问数据。。。。在上面的例子中。。我将所有记录添加到另一个表。。。但你可以很容易地将当前记录添加到循环中。。。

Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
'open main recordset (TDetPed)
rs.Open "SELECT TDetPed.* FROM TDetPed WHERE CodPed = " & CodPed & "", cnn, adOpenKeyset, adLockOptimistic

'open clone table recordset (TDetPedTemp)
rs2.Open "TDetPedTemp", cnn, adOpenKeyset, adLockOptimistic
'move to first record of main table
rs.MoveFirst
'add record by record in clone table(rs2) from maintable(rs)
Do Until rs.EOF
rs2.AddNew
rs2("CodPed") = rs("CodPed")
rs2("CodDetPed") = rs("CodDetPed")
rs2("CodInterno") = rs("CodInterno")
rs2("DescrDetPed") = rs("DescrDetPed")
rs2("DescontoDetPed") = rs("DescontoDetPed")
rs2("CodProd") = rs("CodProd")
rs2("PreçoDetPed") = rs("PreçoDetPed")
rs2("QtdeDetPed") = rs("QtdeDetPed")
'update current added record in clone table
rs2.Update
'move to next record in main table
rs.MoveNext
'Move para o proximo registro do detalhe do pedido
Loop
'close everthing
rs.Close
rs2.Close
cnn.Close
'clean everthing
Set rs = Nothing
Set rs2 = Nothing
Set cnn = Nothing

Answer 2

刚刚完成答案。。。一个使用DAO（数据访问对象）而不是ADO（活动数据对象）的简单示例。（更多信息https://msdn.microsoft.com/en-us/library/aa261340%28v=vs.60%29.aspx/http://www.utteraccess.com/wiki/index.php/Choosing_between_DAO_and_ADO)

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = dbsNorthwind.OpenRecordset("Tbl1")    
    rs.AddNew
    rs!Cidade = "Curitiba"
    rs!Country = "Brazil"       
    '.... others fields    
    rs.Update