Description:
In the test function the statement:
recset1.UpdateBatch
results in error:
[MySQL][MyODBC 5.00.12][MySQL] 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`ado_test` SET `C2`='test ' WHERE `c1`=1 AND `C2`='foo' at line 1 -2147467259
From the general log it appears that the table is qualified in an unusual way...
UPDATE `def`.`ado`.`ado_test` SET `C2`='test ' WHERE `c1`=1 AND `C2`='foo'
ado is the name of the DB, and ado_test is the name of the table, not sure where "def" is coming from. This test works fine in MyODBC 3.51
How to repeat:
SETUP:
mysql> create database ado;
mysql> use ado;
mysql> create table ado_test (c1 int primary key, C2 char(10));
mysql> insert into ado_test values (1, 'foo'), (2, 'bar');
VB CODE:
Private 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 * FROM ado_test"
strConn = "DRIVER={MySQL Connector/ODBC v5};" & _
"SERVER=localhost;DATABASE=ado" & _
";UID=root;PWD=mypass;OPTION=3;"
' open connection
connection1.ConnectionString = strConn
connection1.CursorLocation = adUseClient
connection1.Open
recset1.Open strSQL, connection1, adOpenStatic, _
adLockBatchOptimistic, adCmdText
recset1.MoveFirst
For Each fld In recset1.Fields
If fld.Name = "C2" Then
fld.Value = "test"
End If
Next
recset1.UpdateBatch
Exit Sub
EH:
Debug.Print Err.Description & " " & Err.Number & vbNewLine
End Sub
Description: In the test function the statement: recset1.UpdateBatch results in error: [MySQL][MyODBC 5.00.12][MySQL] 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`ado_test` SET `C2`='test ' WHERE `c1`=1 AND `C2`='foo' at line 1 -2147467259 From the general log it appears that the table is qualified in an unusual way... UPDATE `def`.`ado`.`ado_test` SET `C2`='test ' WHERE `c1`=1 AND `C2`='foo' ado is the name of the DB, and ado_test is the name of the table, not sure where "def" is coming from. This test works fine in MyODBC 3.51 How to repeat: SETUP: mysql> create database ado; mysql> use ado; mysql> create table ado_test (c1 int primary key, C2 char(10)); mysql> insert into ado_test values (1, 'foo'), (2, 'bar'); VB CODE: Private 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 * FROM ado_test" strConn = "DRIVER={MySQL Connector/ODBC v5};" & _ "SERVER=localhost;DATABASE=ado" & _ ";UID=root;PWD=mypass;OPTION=3;" ' open connection connection1.ConnectionString = strConn connection1.CursorLocation = adUseClient connection1.Open recset1.Open strSQL, connection1, adOpenStatic, _ adLockBatchOptimistic, adCmdText recset1.MoveFirst For Each fld In recset1.Fields If fld.Name = "C2" Then fld.Value = "test" End If Next recset1.UpdateBatch Exit Sub EH: Debug.Print Err.Description & " " & Err.Number & vbNewLine End Sub