sql server linked table not accurate

[okschlaps]

I have an Access database linked to tables and views on SQL Server. I have a
table with a column of datatype decimal(12,9). The problem is I do not get
that precision in the linked table. The values are stored in SQL Server
correctly, but I can only see them rounded to 2 decimal points in Access,
both in the table and in forms based on the table. This affects a calculation
I need performed. I have checked my formatting in Access. And can key the
number in Access, but it reverts to the 2-decimal point format and my
calculations are wrong. To complicate matters further, I have a view based on
the table which displays the data correctly, but I can't use this view for
the initial entry.
Thanks for your help.

 

[Ryan]

You can set the number of decimal places in a query. If you open the query
in design view, then right click on the field you are having problems with,
and in the properties of that field you can choose how many decimal places to
show. You can do this in a table, but not a linked SQL Table. Build a query
in access on your linked table, set the decimal places on the field you are
having problems with, and then build your form on the new query.

 

[okschlaps]

I see 9 decimal places, but the values are still rounded - not reflecting
what's actually in the table. It just right filled with 0's.

 

[Ryan]

Try this
=Format([TheNameOfYourField],#,###.#########)

 

[Stefan Hoffmann]

hi,

I have an Access database linked to tables and views on SQL Server. I have a
table with a column of datatype decimal(12,9). The problem is I do not get
that precision in the linked table. The values are stored in SQL Server
correctly, but I can only see them rounded to 2 decimal points in Access,
both in the table and in forms based on the table.

Check your linked table. Switch to the design view and check the format
property.

These properties can be set also for linked tables.


mfG
--> stefan <--

 

[okschlaps]

That's the weird thing. They are set. I'm using data type decimal. Is that
okay for Access? with precision of 9. It still rounds to two decimal places
and just right fills with 0's. I can enter the data fine and it's stored okay
in SQL Server, but when I go back to view it in table or form, it won't show
me the actual number.
Thanks.

 

[Stefan Hoffmann]

hi,

That's the weird thing. They are set. I'm using data type decimal. Is that
okay for Access? with precision of 9. It still rounds to two decimal places
and just right fills with 0's. I can enter the data fine and it's stored okay
in SQL Server, but when I go back to view it in table or form, it won't show
me the actual number.

Check you MDAC version, check also the installed SQL Server driver.

Run a SQL Profiler trace to see whether you get the correct data delivered.

Have you linked the table directly or do you link a view?


mfG
--> stefan <--

 

[okschlaps]

I'm not familiar with that type of trace. Do you know where I can find some
info on it? I have both the table linked and a view based on the table (with
other tables joined). I get the correct info in the view, but not the table.
I need the table to be able to insert and update.