Linked tables; decimal instead of double 1

[blom0344]

We have a curious issue with linked tables (from DB2 UDB to Access)
All relevant fields from DB2 tables were designated as 'double' in Access, causing proper handling of decimal values. However, when I currently link a table the field property is designated as 'decimal' and a value as 123,45 is shown as 12345.
Since this is a linked table I cannot change the scale of the linked field. Every query based on newly linked tables shows values 100 times multiplied....
Anyone an idea?

Ties Blom
Information analyst

 

[MoLaker]

I'm confused here. 123,45 is the same value as 12345, just no comma separator. Did you mean 123.45?

 

[blom0344]

Yes and no. With our (Dutch) languages settings the decimal sign is set as ',' and not as '.'
So where I expect

Onehundredand twentythreepoint four five 123,45

I get

Twelvethousandthreehundredandfourtyfive 12345

Which is a hundred times too high..........

Ties Blom
Information analyst

 

[MoLaker]

Aha! Thanks for the clarification. I am not familiar with Access under different languages.

I was messing with changing datatypes back and forth from double to decimal to see if I could duplicate your condition using a "." as the decimal indicator. I cannot. But, I do not have a DB2 table to link to.

To help troubleshoot your problem, if you have Visual Basic or some language other than MS Access whereby you can extract data from the DB2 table, you might try grabbing the field in question to see if it returns with the proper decimal place. If it returns the same as Access, it might be a DB2 issue - or perhaps a DB2 ODBC driver issue. If it returns what you expect, it might be a MS Access Multilingual pack issue.

 

[blom0344]

Thanks Mo.

What I deduced sofar is the following:

1. Database applications that had linked tables in Access97 and were converted to Access2000 still work properly.
The fieldsizes of amount that are set in DB2 as decimal(x,y) are read as 'double' in Access and correct representation is shown.

2. Creating a new Access2000 database with a linked table to DB2 results in every decimal field being shown as integer, like I described above. In the properties of the linked table the precision and scale are properly detected, but the 'decimal places' is set at auto.

It looks like there is a fundamental difference between access97 and access2000 with regards to linked tables to DB2. Gonna do some posting on DB2 related fora....

Ties Blom
Information analyst

 

[MoLaker]

Here's a link to a MS article that might relate. It applies to OLE, but there is a similar article on ODBC.

http://msdn.microsoft.com/library/d...s_using_the_ole_db_provider_for_db2_oledb.asp

The article seems to apply to both A97 & A2K, but ODBC or OLE driver differences or MDAC level may come into play. Chances are these were of different versions when you were using A97.

 

[blom0344]

Mo, I'll investigate into the link you provided. Here's a star for the effort....

Ties Blom
Information analyst