Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).

Please, please help, kind database community!!

share|improve this question
    
Some additional observations: 1) 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) 2) if i change data type in Oracle from NUMBER(precision, scale) to Double Precision, values come through ok 3) if i change data type in Oracle from NUMBER(precision, scale) to NUMBER without specifying precision or scale, Access displays values just fine 4) in both later cases Access recognizes such fields as Number data type and Double field size –  Marko Jan 16 '13 at 12:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.