ms access converting oracle's number field to text

[ramesh.elapakur]

Hi,
Can anybody help me ?
I've a case where ms access displays oracle's numeric field as text where msaccess is linked to oracle.The possible reason could be when we don't specify precision in declaring the filed in oracle table, default precision is 38 which ms access cannot hold. So, it(driver) converts this to text field.But this is with MSORCL32.dll(microsoft driver for oracle).Is there any other possible reason that will result this behavior?

The following are the configuration details:
1.Oracle 9i on Unix server.
2.MSAccess on Windows XP.
MSAccess holds linked data of Oracle server.

Thanks Inadvance.
Ramesh

 

[ramesh.elapakur]

Thanks for our reply.
Yes, MS Access will convert into text if it can't hold that big numeric.In my case that is not the reason as i can see numeric data in ms access as well. Is there any other reason?
Hope i'm clear in explaining my problem, if not let me know.
Ramesh

 

[The_Doc_Man]

Is the intended data type an integer or a real ?

In any case, your Access solution is to not care about what ORACLE thinks it is. For display purposes, it is going to be converted to text anyway. And for those few cases where a number REALLY needs to be a number, use the appropriate conversion function, as CDbl or CLng or whatever format you wanted. You can put that in a query easily enough.

As to the reason, ... Access is Access. It is notorious for thinking that a given data type is something else, particularly when a Wizard is involved. I'm not surprised to see that a conversion drive does the same thing.

A fix that has worked with me for data imports is to not let Access have the chance to misunderstand the data type in the first place. If you can't tell Access that the field is numeric then don't let ORACLE impose the default field size. Force ORACLE to hold the number in the right format.

If ORACLE cannot be made to change and if the ORACLE-based schema can't be made to change, you will ALWAYS be looking at workarounds.

 

[ramesh.elapakur]

I'm linking to the oracle tables using my vb application where i'm using tabledef to create a equallent oracle table in ms access.DAO will take care of creating tables in ms access using msjet engine.

FYi

Dim tdf As TableDef

tdf.SourceTableName="xxx"(this is oracle table)
ReportDB.TableDefs.append tdf
....

This will create table.

One more thing is this is not heppening everytime.

 

[The_Doc_Man]

If you are making a table from the linked ORACLE tables, you can try making the tables from a QUERY that contains CLng or CDbl or CSngl or CInt or whatever you wanted as a numeric format.