The decimal fields precision is too small to accept the numeric you attempted to add ERROR

Manuel Cavero 1 Reputation point
2021-09-28T07:51:18.097+00:00

Hi everybody

I'm trying to save some records in my Access 2019 1808 (Compilation 10378) Db, but unfortunately I can't. In the process Batch Updating the Database generates an field precision error. I've read some questions about the issue but no one seems to be enought clearly, almost for me.. The error comes when I try to update the Database, not when I populate the recordset

Let me ilustrate the issue:

135798-captura3.png

In the upper right of the image we can see the RecordSet field type, which is Decimal. In the left botton side, we can see more detail of the RecordSet data type, which is adNumeric, with a Definited Size of 19, a Precision of 18 and finally a NumericScale of 12. Finally, in the right botton side, we can see the value of the variable, which is 10.

When try to udapte the db, I'm getting the error defined in the title of this question.

I'm using an ADO Recorset. To update the db values I'm doing the followings steps:

1) Load Query value as string

2) Open a Connection to database

    Set Conn = New ADODB.Connection   
    With Conn  
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source= " & Path   
        .CursorLocation = adUseClient  
        .Mode = adModeReadWrite  
        .Open  
    End With  

3) Open a ADO RecordSet

Set rs = New ADODB.Recordset   
With rs  
    .ActiveConnection = Conexión   
    .CursorLocation = adUseClient   
    .CursorType = adOpenDynamic   
    .LockType = adLockBatchOptimistic   
    .Source = Query   
    .Open   
End With  

4) Add a Record to the RecordSet

rs.AddNew  

5) Validate the data type and populate the RecordSet

135735-captura2.png

6) Update the database

rs.UpdateBatch  
Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
853 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Gustav 712 Reputation points MVP
    2021-09-28T13:43:55.39+00:00

    You have asked this recently. Please provide a link to that question and its comments.


  2. Manuel Cavero 1 Reputation point
    2021-09-28T13:52:03.083+00:00

    Hi Gustav,

    Yes. I posted the same question on this link:

    5a0cd7f8-99d1-4881-9ac1-2e60a2564011

    But no one told me anything

    0 comments No comments

  3. Manuel Cavero 1 Reputation point
    2021-10-04T10:55:01.29+00:00

    Hi everybody

    Now I know that the problem of the number of decimal places is related to the regional configuration of the system. In my case Western Europe.
    Let's see what is happening. First of all I have an Access table with some fields, one of them of type string, another of type double and the important one of type decimal.
    The important field is called Total, which is of numeric type and Decimal format, with a precision of 28 and a scale of 6 (digits to the right of the period or comma). Let's see:

    137384-captura-1.png

    This table has 4 records:

    137403-captura-2.png

    Ok, now we load the table in a Recordset and take it to a ListBox of a form. This is the result:

    137339-captura-3.png

    As you can see, nothing strange happens, because the Array an the table are the same...

    Now, let's do the opposite work, instead of loading, lets go to save this array into the DataBase. First of all into the Recordset, and finally into the Table. So this is the RecordSet object, after populating:

    137413-captura-4.png

    So, if you can see inthe picture, the last value of Total is in the RecordSet, with a value of 99.999,123456 (remember I use comma as decimal separator)

    And finally the new values of the array save into de Access table:

    137340-captura-5.png

    So, here the problem is Access doesn't understood the regional value or the comma as decimal separator, or does it?

    0 comments No comments

  4. Manuel Cavero 1 Reputation point
    2021-10-11T06:42:32.343+00:00

    Any comments? Any anwers?, come on

    0 comments No comments