I have an Access 2003 database (running on Win 7 64-bit, Office is also 64-bit) that has many linked tables pointing to an Oracle 11g instance through 64-bit Oracle ODBC driver. Most of the tables work fine, but some of them keep throwing at me this error: "The decimal field's precision is too small to accept the numeric you attempted to add"
So far i have:
- extensively surfed the web for a solution, but none apply in my case (my ODBC driver has no "enforce double" parameter, other suggestions i found were simply irrelevant)
- checked that Access sets the correct precision and scale for numeric fields (for example, NUMBER(20,10) in oracle maps to Number data type, Decimal field size with precision 20 and scale 10 in Access). This is even true for the fields that generate errors.
- concluded that if a record has at least 1 problematic field, all other fields in that record also display "#Error" in Datasheet view. The record is displayed normally if i set the problematic field to null.
- figured that in my case the problem revolves about fields with WGS coordinates of type NUMBER(20,10) or NUMBER(8,6) (probably also other high scale numeric fields, not sure)
- found out that if i round a problematic field in Oracle to the field's original scale, Access stops complaining. For example: in an Oracle view a NUMBER(20,10) field_name becomes round(field_name, 10), which makes Access happy.
I can't use the rounding workaround on tables and i don't have much freedom altering the Oracle db since it is a part of a very large and complex system.
I'm a newbie in regards of MS Access and have ran out of ideas on what else to try (except burning my computer and becoming a Jogi to curb my desperation).
Some additional observations:
no matter what precision and scale are set in Oracle for the NUMBER field, as soon as i enter a decimal value, the error pops up (whole numbers come through ok)
if i change data type in Oracle from NUMBER(precision, scale) to Double Precision, values come through ok
if i change data type in Oracle from NUMBER(precision, scale) to NUMBER without specifying precision or scale, Access displays values just fine
in both later cases Access recognizes such fields as Number data type and Double field size