在VBA中深入复制或克隆ADODB记录集

问题描述

我一直在寻找在VBA中复制或复制记录集的方法。而且我的意思是让不相干的数据彼此独立。

I have been searching for a way of duplicating or copying a recordset in VBA. And by that I mean, having the undelying data independent of each other.

我尝试过

Set copyRS = origRS.Clone
Set copyRS = origRS

当我使用任何方法，我不能修改一个记录集而不修改另一个。所以在这个例子中：

When I use any of the methods I cant modify one recordset without modifying the other. So in this example:

1. 我创建一个记录集


2. 我填充名为John的记录集


3. 我克隆记录集


4. 我修改克隆的


5. 检查结果

1. I create a recordset
2. I populate the recordset with the name John
3. I clone the recordset
4. I modify the cloned one
5. Check result

代码：

Dim origRS As Recordset, copyRS As Recordset
Set origRS = New Recordset
'Create field
origRS.Fields.Append "Name", adChar, 10, adFldUpdatable
origRS.Open
'Add name
origRS.AddNew "Name", "John"
'Clone/copy
Set copyRS = origRS.Clone
'Change record in cloned/copied recordset
copyRS.MoveFirst
copyRS!Name = "James"
'This should give me "JamesJohn"
MsgBox copyRS.Fields(0).Value & origRS.Fields(0)

但不幸的是，我修改了两个记录集

But unfortunately for me, this modifies both recordsets

我的问题是：

有没有办法从另一个记录集复制记录集，然后独立地修改数据（没有循环）？

Is there a way of copying a recordset from another recordset and then modify the data independently of each other (without looping)?

我知道你显然可以通过一个循环来做，但是没有别的方法吗？

I know that evidently you can do it through a loop, but is there no other way?

推荐答案

++好问题！顺便说一句。这种复制对象的方法称为。

++ Good question! btw. this way of copying object is called a deep copy.

我通常会创建一个 ADODB.Stream 并保存当前的记录集。

I usually get away with creating an ADODB.Stream and saving the current recordset into it.

然后，您可以使用新的记录集的 .Open（）方法，并将流传递给它。

Then you can use the .Open() method of a new recordset and pass the stream to it.

例如：

Sub Main()

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    rs.Fields.Append "Name", adChar, 10, adFldUpdatable
    rs.Open
    rs.AddNew "Name", "John"

    Dim strm As ADODB.Stream
    Set strm = New ADODB.Stream

    rs.Save strm

    Dim copy As New ADODB.Recordset
    copy.Open strm

    copy!Name = "hellow"

    Debug.Print "orignal recordset: " & rs.Fields(0).Value
    Debug.Print "copied recordset: " & copy.Fields(0).Value

    strm.Close
    rs.Close
    copy.Close

    Set strm = Nothing
    Set rs = Nothing
    Set copy = Nothing

End Sub

结果如预期：

这篇关于在VBA中深入复制或克隆ADODB记录集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！