linked SQL table decimal value is truncated in Access 2003 1

[codegirl63]

We have an Access 2003 database that has tables linked from our SQL server (SQL 2003) database. The SQL database stores data from our HRIS system. We recently upgraded from SQL 7 and Access 97 to this newer environment.

In one of the tables we have a field, which is a numeric field with 4 decimal places. It has all of the values after the decimal when we query it through Query Analyzer, but when we link it to Access, it is truncating the value at 2 decimal places. It is not rounding, it is truncating. This is causing us additional work in our payroll processing because we use this database as part of the timesheet process.

We did not have this issue in Access 97. Does anyone have any ideas of what might have changed in Access 2003?
We cannot import the table at this point, we are getting an error about the decimal precision. We are working on that as well to try to determine which rows are causing us that error, it is not our jet engine version, we verified that yesterday.

Any help would be appreciated. Sorry this is so long, just trying to be clear.

 

[PHV]

Try a Money data type in SQL Server and relink the access table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[codegirl63]

Thanks for the help. We found it was the regional settings combined with the ODBC settings that were causing the problem. We have tested it and made the changes for everyone.