Apache OpenOffice (AOO) Bugzilla – Issue 115401
copytablewizard: currency fields from MS Access loose decimal places
Last modified: 2013-01-29 22:01:37 UTC
Copy MS Access table containing currency fields to ODBC databases or internal database. The destination table will be created with a NUMERIC (internal database, mssql, mysql) or DECIMAL (oracle 10) field type, which is fine, but with zero decimal places. The Access currency format is defined as having 4 decimal places. Test file attached.
Created attachment 72853 [details] MS Access table containing currency field
I checked in OOo 3.3 RC3 on win7. During the copy table wizard you can set the decimal places, and copy all data to odb file. Not use copy table wizard, without checking the settings. I set it is as invalid.
Invalid closing.
Of course you can change it manually for every currency field... But why would a wizard propose 0 decimals for a field that has always 4 decimal places? I hit this problem when transferring a 20+ table database... Together with issue 115398 which forces to change all integers, copying tables from access becomes very cumbersome. Manually tuning certain odd fields is acceptable, but modifying every integer and currency field isn't.
I did some further investigation into what ADO reports as NUMERIC_PRECISION and NUMERIC_SCALE by writing a small c# program that retrieves and prints Connection.GetSchema(OleDbMetaDataCollectionNames.Columns) data for the test database attached. They are respectively 19 and 0 for currency fields. This is probably the reason why the wizard proposes 0 decimal places. But... NUMERIC_SCALE has apparently only a meaning for data types that have settable scales like the numeric and decimal types and not for fixed scale data types such as currency. Microsoft in its integration services (platform to convert other databases to SQLServer = copytablewizard from MS ;) ) defines currency scale as 4. See http://msdn.microsoft.com/en-us/library/ms141036.aspx, definition of DT_CY. At the bottom of that page, the JET currency data type is mapped to DT_CY... It isn't a coincidence that DT_CY has numeric value 6 which is the same as OleDbType.Currency or adCurrency... "Hard-wiring" the scale of currency to 4 seems to be the solution to this issue.