5

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!!

  • 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
3

I had a similar problem, the fix was a linked table refresh. Access was not aware that the precision had changed in my Oracle table and kept giving me the 'precision too small' error.

  • This was the solution in my case, too. Right clicked the linked table, Linked Table Manager, select the table, click Ok. – root Jan 19 '18 at 18:54
0

I am running Access 2010 with linked MySQL tables. I discovered that the destination table had a field formatted as "Decimal" and a length of 3,0.

I changed this to 4,0 in MySQL, refreshed linked tables, and the append to the table worked.

So it appears the destination field was not wide enough for the resulting data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.