Description:
In the test code below, if you open recset2 as Keyset, it falls back to ForwardOnly (as currently expected), and it says that it supports adResync. However if you call Resync() it returns this error:
"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. -2147217887"
If instead you open recset2 as ForwardOnly, or Dynamic and you still get a ForwardOnly cursor, but this time the driver will say that adResync is not supported.
It isn't clear from this behavior whether the driver is meant to support adResync or not.
How to repeat:
SETUP:
DROP TABLE IF EXISTS ado_test;
CREATE TABLE ado_test (C1 INT PRIMARY KEY, C2 CHAR(20));
INSERT INTO ado_test VALUES (1, 'A'), (2,'B');
VB CODE:
Private Sub Command4_Click()
On Error GoTo EH
Dim strSQL, strConn As String
Dim connection1 As ADODB.Connection
Dim recset1 As ADODB.Recordset
Dim recset2 As ADODB.Recordset
Dim fld As Field
Set connection1 = New ADODB.Connection
Set recset1 = New ADODB.Recordset
Set recset2 = New ADODB.Recordset
strSQL = "SELECT C1, C2 FROM ado_test"
strConn = "DRIVER={MySQL Connector/ODBC v5};" & _
"SERVER=localhost;DATABASE=ado" & _
";UID=root;PWD=mypass;OPTION=35;"
' open connection
connection1.ConnectionString = strConn
connection1.CursorLocation = adUseServer
connection1.Open
recset1.Open strSQL, connection1, adOpenDynamic, adLockOptimistic, adCmdText
recset2.Open strSQL, connection1, adOpenKeyset, adLockOptimistic, adCmdText
Debug.Print "curtype: " & recset1.CursorType
recset1.MoveFirst
recset1.Fields(1).Value = "test"
recset1.Update
If (recset2.Supports(adResync)) Then
recset2.Resync
End If
recset2.MoveFirst
Debug.Print recset2.Fields(1).Value
recset1.Close
Set recset1 = Nothing
recset2.Close
Set recset2 = Nothing
connection1.Close
Set connection1 = Nothing
Exit Sub
EH:
Debug.Print Err.Description & " " & Err.Number & vbNewLine
Resume Next
End Sub
Description: In the test code below, if you open recset2 as Keyset, it falls back to ForwardOnly (as currently expected), and it says that it supports adResync. However if you call Resync() it returns this error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. -2147217887" If instead you open recset2 as ForwardOnly, or Dynamic and you still get a ForwardOnly cursor, but this time the driver will say that adResync is not supported. It isn't clear from this behavior whether the driver is meant to support adResync or not. How to repeat: SETUP: DROP TABLE IF EXISTS ado_test; CREATE TABLE ado_test (C1 INT PRIMARY KEY, C2 CHAR(20)); INSERT INTO ado_test VALUES (1, 'A'), (2,'B'); VB CODE: Private Sub Command4_Click() On Error GoTo EH Dim strSQL, strConn As String Dim connection1 As ADODB.Connection Dim recset1 As ADODB.Recordset Dim recset2 As ADODB.Recordset Dim fld As Field Set connection1 = New ADODB.Connection Set recset1 = New ADODB.Recordset Set recset2 = New ADODB.Recordset strSQL = "SELECT C1, C2 FROM ado_test" strConn = "DRIVER={MySQL Connector/ODBC v5};" & _ "SERVER=localhost;DATABASE=ado" & _ ";UID=root;PWD=mypass;OPTION=35;" ' open connection connection1.ConnectionString = strConn connection1.CursorLocation = adUseServer connection1.Open recset1.Open strSQL, connection1, adOpenDynamic, adLockOptimistic, adCmdText recset2.Open strSQL, connection1, adOpenKeyset, adLockOptimistic, adCmdText Debug.Print "curtype: " & recset1.CursorType recset1.MoveFirst recset1.Fields(1).Value = "test" recset1.Update If (recset2.Supports(adResync)) Then recset2.Resync End If recset2.MoveFirst Debug.Print recset2.Fields(1).Value recset1.Close Set recset1 = Nothing recset2.Close Set recset2 = Nothing connection1.Close Set connection1 = Nothing Exit Sub EH: Debug.Print Err.Description & " " & Err.Number & vbNewLine Resume Next End Sub