README.md

Description

PROBLEM:

Creating a copy of a Recordset could mean using the Recordset.Clone method sometimes. However, this is not always appropriate because ADO’s Recordset.Clone method creates a Recordset which points to the same data as the original Recordset. This means that you really do not get a physically distinct copy of the original Recorsdet. Consequently, changes to the clone copy could modify the data in the original Recordset.

An alternative solution would be to use a loop to append each record to a new recordset. This, however could end up being an unwieldy solution with potential performance penalties.

SOLUTION:

ADO 2.5 comes with a Stream object that can be used to create physically distinct copies of Recordsets. The article below demonstrates the use of the ADO 2.5 Stream object in copying Recordsets and the difference between the Recordsets created by the Recordset.Clone method and ADO 2.5 Stream object.

Please see attached file.

More Info

Submitted On	2002-07-09 21:50:22
By	visual-basic-data-mining.net
Level	Intermediate
User Rating	4.5 (36 globes from 8 users)
Compatibility	VB 6.0
Category	Databases/ Data Access/ DAO/ ADO
World	Visual Basic
Archive File	RECORDSET_104146792002_5_STREAM_OBJECT_AND.zip

Source Code

RECORDSET COPYING USING ADO 2.5 STREAM OBJECT

PROBLEM:
Creating a copy of a Recordset could mean using the Recordset.Clone method sometimes. However, this is not always appropriate because ADO’s Recordset.Clone method creates a Recordset which points to the same data as the original Recordset. This means that you really do not get a physically distinct copy of the original Recorsdet. Consequently, changes to the clone copy could modify the data in the original Recordset.

An alternative solution would be to use a loop to append each record to a new recordset. This, however could end up being an unwieldy solution with potential performance penalties.

SOLUTION:
ADO 2.5 comes with a Stream object that can be used to create physically distinct copies of Recordsets. The article below demonstrates the use of the ADO 2.5 Stream object in copying Recordsets and the difference between the Recordsets created by the Recordset.Clone method and ADO 2.5 Stream object.

The Clone() Function below demonstrates a typical use of the Recordset.Clone method.

Function Clone (ByVal rstSource As Adodb.recordset) As Adodb.Recordset
'Create a copy of a Recordset using ADO's Recordset.Clone method

Dim rstCopy As ADODB.Recordset

Set rstCopy = rstSource.Clone

Set Clone = rstCopy

End Function

Since both the initial Recordset and the cloned copy point to the same data structure, data changes like adding and deleting records made to the cloned copy will also take place on the original Recordset and vice versa. So your clone is not really a separate physical copy of the original Recordset.

How about the ADO 2.5 Stream object?
With ADO 2.5, you can create a separate physical Recordset object using the Stream object and XML. It is really very simple as the following Function shows.

Function Copy (ByVal rstSource As Adodb.recordset) As Adodb.Recordset
'Create a copy of a Recordset using AD0 2.5 Stream Object and XML

Dim rstCopy As ADODB.Recordset
Dim objStream As ADODB.Stream

'Create a New ADO 2.5 Stream object
Set objStream = New ADODB.Stream

'Save the Recordset to the Stream object in XML format
rstSource.Save objStream, adPersistXML

'Create an exact copy of the saved Recordset from the Stream Object
Set rstCopy = New ADODB.Recordset
rstCopy.Open objStream

'Close and de-reference the Stream object
objStream.Close
Set objStream = Nothing

Set Copy = rstCopy

End Function

The article continued below demonstrates the use of the Recordset.Clone method and the ADO Stream object to copy Recordsets and test the copied Recordsets for references to their original Recordsets.

Let’s start by creating two Fabricated Recordsets with each containing 3 rows of names. We will also create two copies of the Recordset using the Recordset.Clone method and the ADO Stream object. One name will be deleted from each copied Recordset and finally the original Recordsets will be compared to their copies to see if they were indeed true physically distinct Recordset copies.

Sub CreateDistinctRecordsetCopy()
'Compare ADO's Recordset.Clone and ADO 2.5 Stream object’s Recordset copying methods


Dim rstOne As ADODB.Recordset
Dim rstTwo As ADODB.Recordset
Dim rstClone As ADODB.Recordset
Dim rstCopy As ADODB.Recordset


'Create two Fabricated Recordsets
Set rstOne = CreateFabricatedRecordset

Set rstTwo = CreateFabricatedRecordset


'Create a cloned copy of the Recordset using ADO's Recordset.Clone method
Set rstClone = Clone(rstOne)

'Create a copy of the Recordset using the ADO 2.5 Stream object
Set rstCopy = Copy(rstTwo)


'Delete a record from both Recordset copies
rstClone.Delete
rstCopy.Delete


'If the cloned Recordset and it's original contain the same number of records then ADO Recordset.Clone copies
'and their original recordsets point to the same data structures and are not completely distinct copies.

If (rstOne.RecordCount = rstClone.RecordCount) Then

MsgBox "Recordset.Clone Copies Are Not Completely Separate Objects From Their Original Recordsets"

Else

MsgBox "Recordset.Clone Copies Are Completely Separate Objects From Their Original Recordsets"

End If


'If the Recordset copied using ADO 2.5 Stream object and it's original contain differing number of records then
'ADO 2.5 Stream object Recordset copies are completely distinct copies of the original recordsets.

If (rstTwo.RecordCount = rstCopy.RecordCount) Then

MsgBox "ADO 2.5 Stream Recordset Copies Are Not Completely Separate Objects From Their Original Recordsets"

Else

MsgBox "ADO 2.5 Stream Recordset Copies Are Completely Separate Objects From Their Original Recordsets"

End If

End Sub


Function CreateFabricatedRecordset() As ADODB.Recordset
'Creates a Fabricated Recordser populated with names

Dim rst As ADODB.Recordset
Dim varField As Variant


'Create a Fabricated Recordset
Set rst = New ADODB.Recordset

With rst.Fields
.Append "LastName", adVarChar, 20
End With

'Open the recordset
rst.Open


varField = Array("LastName")


'Populate the fabricated Recordset with 3 names
With rst
.AddNew varField, Array("John")
.AddNew varField, Array("Paul")
.AddNew varField, Array("King")
End With


'Move to the first record
rst.MoveFirst


'Return the created Recordset
Set CreateFabricatedRecordset = rst

End Function

ATTACHED FILES:
The Visual Basic implementation of this source code and a Microsoft Word(c) 2000 documentation of the project.


CONCLUSION:
The ADO 2.5 Stream object can be used to create distinctly separate Recordset objects which the ADO Recordset.Clone method cannot do.


AUTHOR:
Kingsley is a Technology Consultant specializing in Business Intelligence and can be reached at his site http://www.visual-basic-data-mining.net

http://www.visual-basic-data-mining.net is a site dedicated to Visual Basic Data Mining Source Code distribution.Kingsley has made the Data Mining Source Code freely available to the public at http://www.visual-basic-data-mining.net