Description:
When I insert a row using ADO from by VBA application into a decimal field of a MySQL database table, the decimal point is lost. For example, inserting the value 123.45 into a decimal(11,4) field results in the value "1234500" being stored in the database.
In ADO, if I change the CursorLocation to "adUseClient", it works just fine, with no errors.
I am not 100% sure if this is an MDAC bug or an ODBC connector bug, but it looks like ODBC connector to me, since it works OK with an MSDE backend.
How to repeat:
In VBA, create the following routine:
Sub TestDecimal_ERROR()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnn.Open "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=XXXX; PORT=3306; DATABASE=XXXX; OPTION=3;User=XXXX;Password=XXXX;"
cnn.Execute "CREATE TEMPORARY TABLE tmpXYZ(ID int(11) NOT NULL, " & _
"Amount decimal(19,4) NOT NULL, " & _
"PRIMARY KEY (ID))"
rst.Open "SELECT * FROM tmpXYZ", cnn, adOpenDynamic, adLockOptimistic
With rst
.AddNew
!ID = 1
!Amount = 123.45
.Update
End With
Debug.Print "Amount after update is " & Trim(Str(rst!Amount))
rst.Close
rst.Open "SELECT amount from tmpXYZ WHERE ID = 1", cnn, adOpenStatic, adLockOptimistic
Debug.Print "Amount after subsequent retrieval is " & Trim(Str(rst!Amount))
rst.Close
Set rst = Nothing
cnn.Execute "DROP TABLE tmpXYZ"
cnn.Close
Set cnn = Nothing
End Sub
This produces the following output:
Amount after update is 123.45
Amount after subsequent retrieval is 1234500
Suggested fix:
I can't suggest a fix, but a workaround is to set CursorLocation to adUseClient. The following code works fine:
Sub TestDecimal_WORKSOK()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnn.Open "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=XXXX; PORT=3306; DATABASE=XXXX; OPTION=3;User=XXXX;Password=XXXX;"
cnn.Execute "CREATE TEMPORARY TABLE tmpXYZ(ID int(11) NOT NULL, Amount decimal(19,4) NOT NULL, PRIMARY KEY (ID))"
rst.CursorLocation = adUseClient
rst.Open "SELECT * FROM tmpXYZ", cnn, adOpenDynamic, adLockOptimistic
With rst
.AddNew
!ID = 1
!Amount = 123.45
.Update
End With
Debug.Print "Amount after update is " & Trim(Str(rst!Amount))
rst.Close
rst.Open "SELECT amount from tmpXYZ WHERE ID = 1", cnn, adOpenStatic, adLockOptimistic
Debug.Print "Amount after subsequent retrieval is " & Trim(Str(rst!Amount))
rst.Close
Set rst = Nothing
cnn.Execute "DROP TABLE tmpXYZ"
cnn.Close
Set cnn = Nothing
End Sub
This produces the following output:
Amount after update is 123.45
Amount after subsequent retrieval is 123.45
An alternate solution is to use the INSERT statement, which also works fine (e.g. cnn.Execute "INSERT INTO tmpXYZ(ID,Amount) VALUES(1,123.45)") This also stores the correct values into the table.
Description: When I insert a row using ADO from by VBA application into a decimal field of a MySQL database table, the decimal point is lost. For example, inserting the value 123.45 into a decimal(11,4) field results in the value "1234500" being stored in the database. In ADO, if I change the CursorLocation to "adUseClient", it works just fine, with no errors. I am not 100% sure if this is an MDAC bug or an ODBC connector bug, but it looks like ODBC connector to me, since it works OK with an MSDE backend. How to repeat: In VBA, create the following routine: Sub TestDecimal_ERROR() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset cnn.Open "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=XXXX; PORT=3306; DATABASE=XXXX; OPTION=3;User=XXXX;Password=XXXX;" cnn.Execute "CREATE TEMPORARY TABLE tmpXYZ(ID int(11) NOT NULL, " & _ "Amount decimal(19,4) NOT NULL, " & _ "PRIMARY KEY (ID))" rst.Open "SELECT * FROM tmpXYZ", cnn, adOpenDynamic, adLockOptimistic With rst .AddNew !ID = 1 !Amount = 123.45 .Update End With Debug.Print "Amount after update is " & Trim(Str(rst!Amount)) rst.Close rst.Open "SELECT amount from tmpXYZ WHERE ID = 1", cnn, adOpenStatic, adLockOptimistic Debug.Print "Amount after subsequent retrieval is " & Trim(Str(rst!Amount)) rst.Close Set rst = Nothing cnn.Execute "DROP TABLE tmpXYZ" cnn.Close Set cnn = Nothing End Sub This produces the following output: Amount after update is 123.45 Amount after subsequent retrieval is 1234500 Suggested fix: I can't suggest a fix, but a workaround is to set CursorLocation to adUseClient. The following code works fine: Sub TestDecimal_WORKSOK() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset cnn.Open "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=XXXX; PORT=3306; DATABASE=XXXX; OPTION=3;User=XXXX;Password=XXXX;" cnn.Execute "CREATE TEMPORARY TABLE tmpXYZ(ID int(11) NOT NULL, Amount decimal(19,4) NOT NULL, PRIMARY KEY (ID))" rst.CursorLocation = adUseClient rst.Open "SELECT * FROM tmpXYZ", cnn, adOpenDynamic, adLockOptimistic With rst .AddNew !ID = 1 !Amount = 123.45 .Update End With Debug.Print "Amount after update is " & Trim(Str(rst!Amount)) rst.Close rst.Open "SELECT amount from tmpXYZ WHERE ID = 1", cnn, adOpenStatic, adLockOptimistic Debug.Print "Amount after subsequent retrieval is " & Trim(Str(rst!Amount)) rst.Close Set rst = Nothing cnn.Execute "DROP TABLE tmpXYZ" cnn.Close Set cnn = Nothing End Sub This produces the following output: Amount after update is 123.45 Amount after subsequent retrieval is 123.45 An alternate solution is to use the INSERT statement, which also works fine (e.g. cnn.Execute "INSERT INTO tmpXYZ(ID,Amount) VALUES(1,123.45)") This also stores the correct values into the table.