FIX: Navigating Through an ADO Recordset Clone May Generate Error -2147217885 (236624)

MORE INFORMATION

Steps to Reproduce Behavior

1. Create a new Visual Basic project. Form1 is created by default.
2. Add a Project Reference to Microsoft ActiveX Data Objects Library 2.0 or Microsoft ActiveX Data Objects Library 2.1.
3. Add a command button to Form1. Command1 is created by default.
4. Paste the following code into the General Declaration section of Form1. Replace sql_server_name with the name of your SQL Server.
   
   Note You must change User ID <User ID> and password <Strong Password> to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.

   Dim rs As ADODB.Recordset
   Dim rsClone As ADODB.Recordset
   Dim Cn As ADODB.Connection
   
   Private Sub Command1_Click()
   
   Set Cn = New ADODB.Connection
   Set rs = New ADODB.Recordset
   
   ' Open the connection
   Cn.Open "Provider=SQLOLEDB;Data Source=sql_server_name;User ID=<User ID>;Password=<Strong Password>;Initial Catalog=Pubs;"
   
   'Open a disconnected ADO recordset
    With rs
       .CursorLocation = adUseClient
       .LockType = adLockBatchOptimistic
       .Open "SELECT * FROM Titles", Cn
   
       'Disconnect the recordset
         Set .ActiveConnection = Nothing
         Cn.Close
         Set Cn = Nothing
    End With
   
   'Perform the Sort/Delete/Clone/MoveNext steps, to generate the error
    With rs
       ' Sort the recordset
         .Sort = "pub_id"
         .MoveLast
       ' Delete current record in the recordset
         .Delete
       ' Take a clone of the recordset
         Set rsClone = .Clone
    End With
   
   ' Filter the recordset Clone
    rsClone.Filter = "pub_id <= 1000"
   
   ' Navigate through the cloned recordset
    With rsClone
       Do While Not .EOF
           ' Error occurs on this next line
            .MoveNext
       Loop
    End With
   
   ' Clean up
    rsClone.Close
    Set rsClone = Nothing
    rs.Close
    Set rs = Nothing
   
    End Sub
   					

5. Run the project. You may get error -2147217885.
6. Comment out either of the following lines of code:

   .Sort = "pub_id"
   					

   -or-

   rsClone.Filter = "pub_id <= 1000"
   					

7. Run the project. You should now be able to run without receiving error -2147217885.