Clone Method Example (VB)

Clone Method Example (VB)

This example uses the Clone method to create copies of a Recordset and then lets the user position the record pointer of each copy independently.

Public Sub CloneX()

    Dim arstStores(1 To 3) As ADODB.Recordset
    Dim intLoop As Integer
    Dim strSQL As String
    Dim strCnn As String
    Dim strMessage As String
    Dim strFind As String

    ' Assign SQL statement and connection string to variables.
    strSQL = "SELECT stor_name FROM Stores " & _
        "ORDER BY stor_name"
        strCnn = "Provider=sqloledb;" & _
        "Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "

    ' Open recordset as a static cursor type recordset.
    Set arstStores(1) = New ADODB.Recordset
    arstStores(1).CursorType = adOpenStatic
    arstStores(1).LockType = adLockBatchOptimistic
    arstStores(1).Open strSQL, strCnn, , , adCmdText

    ' Create two clones of the original Recordset.
    Set arstStores(2) = arstStores(1).Clone
    Set arstStores(3) = arstStores(1).Clone

    Do While True

        ' Loop through the array so that on each pass, 
        ' the user is searching a different copy of the 
        ' same Recordset.
        For intLoop = 1 To 3

            ' Ask for search string while showing where 
            ' the current record pointer is for each Recordset.
            strMessage = _
                "Recordsets from stores table:" & vbCr & _
                "  1 - Original - Record pointer at " & _
                arstStores(1)!stor_name & vbCr & _
                "  2 - Clone - Record pointer at " & _
                arstStores(2)!stor_name & vbCr & _
                "  3 - Clone - Record pointer at " & _
                arstStores(3)!stor_name & vbCr & _
                "Enter search string for #" & intLoop & ":"
            strFind = Trim(InputBox(strMessage))
            If strFind = "" Then Exit Do

            ' Find the search string; if there's no 
            ' match, jump to the last record.
            arstStores(intLoop).Filter = "stor_name >= '" & strFind & "'"
            If arstStores(intLoop).EOF Then
                arstStores(intLoop).Filter = adFilterNone
                arstStores(intLoop).MoveLast
            End If

        Next intLoop

    Loop

    arstStores(1).Close
    arstStores(2).Close
    arstStores(3).Close

End Sub