Data from Oracle table is changed in linked table.

[vinhngu]

Can someone please help me to solve this situation? I try to find out why data is changed from Oracle to Access. My goal is that I want to get data from Oracle DB and produce report in Access 2003.

Here is the problem:
In Access, I created ODBC connection (System DSN) to Oracle 9.2 database by using Oracle driver (NOT Microsoft ODBC for Oracle). Then from Access I linked with the Oracle table. What happens is that the number in Access linked table is displayed as a scientific number. Is there a way that I can preserve whatever I have in Oracle database?

For example:
Comp_ID in Oralce is defined as NUMBER(22,0) = 40000000000324003; In Access it looks like this 4.0000000000324E+16. Access is interpreting this number as DOUBLE and display as an scientific number.

NOTE:
Even though, in Access, I see a scientific number in linked table, but Access actually recognize the number as a whole number, 'cuz when I run a "select" query against a linked table with criteria of comp_id = 40000000000324003, it returns the correct result. However, if I create make-table query, the number in "native" Access table is changed to 40000000000324000.

What I have in my system:
1- Oracle 9.2 client
2- Oracle 9.2 driver
3- Access 2003
4- Jet - SP 8

Thanks

VN

 

[vinhngu]

Can someone please help me to resolve this issue. Many thanks

 

[cmmrfrds]

I am not sure if you can control the mapping of the data types.

Here are a couple of articles.

http://www.openlinksw.com/info/docs/uda50/lite/appendix.html#jetmap

http://www.oracle.com/technology/software/tech/windows/odbc/htdocs/ODBCFAQ.pdf