Copying Access Currency to MySQL Decimal(10,2)
Posted by: danny companez
Date: May 11, 2010 10:20PM

I am using Visual Basic for Access to push data from Access Tables to MySQL tables. The field in Access is defined as Currency, Decimal Places=Auto, and the target in MySQL is DECIMAL(10,2). The value in Access is $1.23. The value after the copy is 123.00. If I define the field in Access as Currency, Decimal Places=2 it still has value of 123.00.

I use ADODB recordsets to do the copy. MySql Connector/ODBC 5.1.
Code fragments below which give you the idea of how I do the data copy.
Set MySqlConn = New ADODB.Connection: MySqlConn.Provider = "MSDASQL"
MySqlConn.ConnectionString = "DSN=test;" 'which points to my MySQL database
MySqlConn.Open
Set MySqlRS = New ADODB.Recordset
MySqlRS.Open strTableName, MySqlConn, adOpenDynamic, adLockOptimistic, adCmdTable
Set rsOLD = currentDB.openRecordset(strTableName,dbOpenTable)
intNbrFields = rsOLD.Fields.Count
DO Until rsOLD.EOF
MySqlRS.addnew
For intIndex = 0 To intNbrFields - 1
strFieldName = rsOLD.Fields(intIndex).name
MySqlRS(strFieldName).Value = rsOLD(strFieldName).Value ' breakpoint here
Next intIndex
MySqlRS.Update
rsOLD.movenext
Loop

If I put a breakpoint on the assign line, it shows the MySqlRS value as 1.23! but when I examine the table in MySQL it shows 123.00.

If I change the assignment to: "MySqlRS(strFieldName).Value = rsOLD(strFieldName).Value / 100" the result in the MySQL table is 1.00.

To get around this problem I identify the columns which are like this, and after the whole table is copied across I issue an "UPDATE table SET field1 = field1 / 100, field2 = field2 / 100 " etc. This now gives me the correct result.

What am I doing wrong?
Thanks

Options: ReplyQuote


Subject
Written By
Posted
Copying Access Currency to MySQL Decimal(10,2)
May 11, 2010 10:20PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.