"Scaling of decimal value resulted in data truncation" via ODBC

Question

I'm receiving an error of "Scaling of decimal value resulted in data truncation" when simply trying to view an ODBC table in MS Access. I know the field that is returning the error, and Access is able to recognize the field when querying off of it, but I'm not able to view the results (#Error records) and the error keeps kicking back.

I've tried CDbl() with no luck. A Nz() doesn't even allow the query to run.

The source database is Oracle, connected via Database Client 11g Release 2 for Microsoft Windows x64. The field is defined by the data dictionary as Number with a length of 22. Design view shows the fields a Decimal with precision of 13, but a scale of 2.

I'm guessing the scale of 2 is causing this error. I'm linking the table via MS Access. Is there anyway for me to resolve this with access to Access and the ODBC, or does this need to be resolved in Oracle?

Answer 1

You can use path though query for show your table as following:

You can find useful information for creating path through query and how to edit on it here:

path through query

Answer 2

I've faced this issue some days ago and thanks to DAPOLE I had the solution which I share...

• Whenever dealing with linked tables in Access and you change the underlying table definition - when creating your front-end you MUST relink your linked tables for the changes to propagate to Access.
• In my case I had changed the linked tables data definition to from numeric to numeric(5,2) but hadn't relinked the tables,
• so on entering or calculating decimal values Access didn't know that it could store them,
• hence the error Error scaling of decimal value resulted in data truncation.

Answer 3

The Oracle server recently changed its scale from 2 to 4 to allow the greater precision. Linked MS Access tables do not update when the change is made on the server. The table must be removed and relinked for the scale to refresh.