ODBC link converting Decimal to Long Int

[Matt Williamson]

I have a view in an OLD SQL7 db that I need to access through MS Access
2003. When I link to the view through ODBC it converts a Decimal (18,4)
to a Long Integer and I don't get decimal places when I look at it in
Access. This is kind of odd, so I created a new table in the SQL7 db
with 2 fields: id and dist_amt. ID is int and dist_amt is Decimal (18,4)
When I link this table through the same ODBC connection, access keeps it
Decimal (18,4) and I can use decimals in it. What might be the
difference and is there any way to force the ODBC connection to use
Decimal and not convert to Long Int?

TIA

Matt

 

[Matt Williamson]

I have a view in an OLD SQL7 db that I need to access through MS Access
2003. When I link to the view through ODBC it converts a Decimal (18,4)
to a Long Integer and I don't get decimal places when I look at it in
Access. This is kind of odd, so I created a new table in the SQL7 db
with 2 fields: id and dist_amt. ID is int and dist_amt is Decimal (18,4)
When I link this table through the same ODBC connection, access keeps it
Decimal (18,4) and I can use decimals in it. What might be the
difference and is there any way to force the ODBC connection to use
Decimal and not convert to Long Int?

TIA

Matt

In case anyone encounters this problem, I figured out how to fix it.
After changing the datatype of the underlying SQL table field I had to
run sp_refreshview on the SQL server. After doing that and then dropping
and re-linking the table, it reads the correct data format in Access.