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

Question

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:

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

6) Update the database

rs.UpdateBatch  

Answer 1

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

Answer 2

Hi Gustav,

Yes. I posted the same question on this link:

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

But no one told me anything

Answer 3

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:

This table has 4 records:

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

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:

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:

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

Answer 4

Any comments? Any anwers?, come on