Access linked tables truncating my Decimal values from the SQL server

Question

Since migrating the Access data to a SQL server I am having multiple problems with the decimal values. In my SQL tables on the SQL 2012 server I am using the Decimal data type for multiple fields. A while a go I first tried to set the decimal values to 18,2 but Access acted weird on this by truncating all the values (55,55 became 50 and so on).

So after multiple changes it seemed that Access accepted the 30,2 decimal setting in the SQL server (now the values were linked correct in the linked Access tables).

A few days ago I stumbled however back on this problem because a user had problems with editing a number in the access form. So I checked the linked table data type and there it seemed that Access converts the decimal 30,2 value to a Short Text data type, which is obviously wrong. So I did a bit of research and found out that Access cannot handle a 30,2 decimal, thus it is converted to text by the ODBC driver. (See my previously post: Access 2013 form field value gets cut off on changing the number before the point)

So to fix this latter error I tried, once again (forgetting that I already messed around with it) to change the decimal value to 17,2 / 18,2 and some other decimal values but on all these changes I am getting back to the truncating problem...

I found some posts about it but nothing concrete or answers on how to solve it.

Some additional information:

• Using a SQL 2012 server
• Using Access 2013
• Got a SQL Server Native Client 10 and 11 installed.
• Looking in the register key I found out that I am using ODBC driver version 02.50
• The SQL native client 11 has/uses DriverODBC ver 03.80 and the native client 10 uses DriverODBC ver 10.00 (not sure this is relevant though).

UPDATE WITH IMAGES

In a access form I have multiple lines that have a linked table (sql table) as record source. These lines get populated with the data in the SQL server. Below you can see a line with a specific example, the eenh. prijs is loaded from the linked (SQL) table.

Now when I change the 5 in front of the point (so making it 2555,00 instead of 5555,00) the value gets cut off:

======>>>

So I did research on it and understand that my SQL decimal 30,2 isn't accepted by Access. So I looked in my access linked table to see what kind of data type the field is:

So the specific column (CorStukPrijs) is in the SQL server a decimal 30,2 but here a short text (sorry for the dutch words). The other numerics (which are OK) are just normal integers by the way.

In my linked table on access - datasheet view the values look like this:

I also added a decimal value of how it looks in my linked table:

In my SQL server the (same) data looks like this:

Though, because of the changing number problem before the point (back in the form - first images) I changed the decimal type of 30,2 in the server to 18,2. This is the result in the linked table on that same 5555 value:

It gives #Errors and the error message:

Scaling of decimal values has resulted in truncated values

(translated it so wont be probably exactly like that in English)

The previous 0,71 value results with the decimal 18,2 in:

Hope its a bit clearer now! P.S. I just changed one decimal field to 18,2 now.

Answer 1

Recently I found a solution for this problem! It all had to do with language settings after all.. (and the decimal 30,2 which is not accepted as a decimal in Access 2013).

I changed the Native client from 10 to 11 and in my connection string I added one vital value: regional=no. This fixed the problem!

So now my connection string is: szSQLConnectionString = "DRIVER=SQL Server Native Client 11.0;SERVER=" & szSQLServer & ";DATABASE=" & szSQLDatabase & ";UID=" & szSQLUsername & ";PWD=" & szSQLPassword & ";regional=no;Application Name=OPS-FE;MARS_Connection=yes;"