Description:
In the code below, an error is hit when the line:
recset1.MoveNext
is encountered the first time to move the record set from the first record to the second record:
"Row handles must all be released before new ones can be obtained. -2147217883"
This test works when the cursor location is set to adUseClient.
How to repeat:
SETUP:
DROP TABLE IF EXISTS ado_test;
CREATE TABLE ado_test (C1 INT PRIMARY KEY, C2 CHAR(20), C3 TIMESTAMP);
INSERT INTO ado_test VALUES (1, 'A'),(2, 'B');
VB CODE:
Sub Test()
On Error GoTo EH
Dim strSQL, strConn As String
Dim connection1 As ADODB.Connection
Dim recset1 As ADODB.Recordset
Dim fld As Field
Set connection1 = New ADODB.Connection
Set recset1 = New ADODB.Recordset
strSQL = "SELECT C1, C2 FROM ado_test"
strConn = "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=localhost;DATABASE=ado" & _
";USER=root;PASSWORD=mypass;OPTION=35;"
' open connection
connection1.ConnectionString = strConn
connection1.CursorLocation = adUseServer
connection1.Open
strSQL = "SELECT C1, C2 FROM ado_test"
recset1.Open strSQL, connection1, adOpenDynamic, adLockBatchOptimistic, adCmdText
Debug.Print "curtype: " & recset1.CursorType
Debug.Print "locktype: " & recset1.LockType
recset1.MoveFirst
If (recset1.Supports(adUpdateBatch)) Then
While Not recset1.EOF
For Each fld In recset1.Fields
If fld.Type = adChar Then
fld.Value = "test"
End If
Next
recset1.MoveNext
Wend
recset1.UpdateBatch
End If
recset1.Close
Set recset1 = Nothing
connection1.Close
Set connection1 = Nothing
Exit Sub
EH:
Debug.Print Err.Description & " " & Err.Number & vbNewLine
Resume Next
End Sub
Description: In the code below, an error is hit when the line: recset1.MoveNext is encountered the first time to move the record set from the first record to the second record: "Row handles must all be released before new ones can be obtained. -2147217883" This test works when the cursor location is set to adUseClient. How to repeat: SETUP: DROP TABLE IF EXISTS ado_test; CREATE TABLE ado_test (C1 INT PRIMARY KEY, C2 CHAR(20), C3 TIMESTAMP); INSERT INTO ado_test VALUES (1, 'A'),(2, 'B'); VB CODE: Sub Test() On Error GoTo EH Dim strSQL, strConn As String Dim connection1 As ADODB.Connection Dim recset1 As ADODB.Recordset Dim fld As Field Set connection1 = New ADODB.Connection Set recset1 = New ADODB.Recordset strSQL = "SELECT C1, C2 FROM ado_test" strConn = "DRIVER={MySQL ODBC 3.51 Driver};" & _ "SERVER=localhost;DATABASE=ado" & _ ";USER=root;PASSWORD=mypass;OPTION=35;" ' open connection connection1.ConnectionString = strConn connection1.CursorLocation = adUseServer connection1.Open strSQL = "SELECT C1, C2 FROM ado_test" recset1.Open strSQL, connection1, adOpenDynamic, adLockBatchOptimistic, adCmdText Debug.Print "curtype: " & recset1.CursorType Debug.Print "locktype: " & recset1.LockType recset1.MoveFirst If (recset1.Supports(adUpdateBatch)) Then While Not recset1.EOF For Each fld In recset1.Fields If fld.Type = adChar Then fld.Value = "test" End If Next recset1.MoveNext Wend recset1.UpdateBatch End If recset1.Close Set recset1 = Nothing connection1.Close Set connection1 = Nothing Exit Sub EH: Debug.Print Err.Description & " " & Err.Number & vbNewLine Resume Next End Sub