mismatch in the field type while using Microsoft ODBC for ORACLE Drivers

[selva]

Hi,
I am facing the following problem after my conversion to
2002.
Brief Description of the Problem:
I am using Access 2002 with backend Oracle 8x.
I am having some SQL-pass through queries with the
connection string points to the DSN.I am using the
Microsoft ODBC Driver for ORACLE for DSN.
In the Access database, I am having the following make
query-
SELECT SQLPassqry.* INTO tblA
FROM SQLPassqry;

Where as SQLPassqry is the SQL Pass through Query.
It creates the table "tblA" with the fields having "Text"
Data Type. Whereas the underlying table fields and it's
Data in ORACLE are "Number".

This is causing "Type mismatch in expression" error. I am
experiencing this problem when i moved the database to
Access 2002 format. It's working fine for Access 97 with
Microsoft ODBC for Oracle drivers.
Please help me with your suggestions

Thanks
Selva

 

[Anne Nolan]

In Oracle, you can specify the size (precision) of Number columns.
Anything over Number(15) will get treated as text by Access. If it's
just NUMBER (no precision), 38 is assumed (I think).

You may need to create your table in Access the way you want it, then
import the data. Or just use your SQLPassqry as a table, and don't
bother moving the data into Access at all.

Hope this helps,

Anne Nolan

 

[Anne Nolan]

I believe it has to do with the limitations of numeric data types in
Access. According to the help file, a Long Integer is 4 bytes (Stores
numbers from –2,147,483,648 to 2,147,483,647 ). That's a NUMBER(10) in
Oracle. Oracle numbers can be bigger than this, so Access changes to
text so that it does not truncate the data. I don't think any driver in
the world will change this.

You can apply a query to your Access text field to convert the text to a
numeric, if you need to do math or sorting with it.

Anne