Access linked tables truncating my Decimal values from the SQL server

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.

Solution 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;"

Solution 2:

A few things:

No real good reason to try a decimal value of 30 digits?

Access only supports 28 digits for a packed decimal column. So going to 30 will force Access to see that value as a string.

If you keep the total digits below 28, then you should be ok.

You also left out what driver you are using. (legacy, or native 10 or native 11). However, all 3 should have no trouble with decimal.

As a few noted here, after ANY change to the sql table, you have to refresh the linked table else such changes will not show up.

There is NO need to have some re-link code every time on startup. And it not clear how your re-link code works. If the re-link code makes a copy of the tabledef object, and then re-instates the same tabledef then changes to the back end may well not show up.

I would suggest during testing, you DO NOT use your re-link routines, but simply right click on the given linked table and choose the linked table manager. Then click on the one table, and ok to refresh.

Also, in Access during this testing, dump (remove) any formatting you have in the table settings for testing (the format setting).

I suggest you start over, and take the original tables and re-up-size them again.

Access should and can handle the decimal types with ease, but it not clear what your original settings were. If the values never require more than 4 significant digits beyond the decimal, then I would consider using currency, but decimal should also work.