By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,109 Members | 969 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,109 IT Pros & Developers. It's quick & easy.

Access 97 linked to Oracle showing wrong datatypes

P: n/a
In an Access 97 (and 2000) database that has linked tables to Oracle
which is showing some fields as text when they are numbers in Oracle.
In Oracle they are specified to a size e.g. Number(38).
This is causing some issues when trying join different tables in a
query.

How can the correct datatype be defined in Access?

Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
ro*******@hotmail.com wrote:
In an Access 97 (and 2000) database that has linked tables to Oracle
which is showing some fields as text when they are numbers in Oracle.
In Oracle they are specified to a size e.g. Number(38).
This is causing some issues when trying join different tables in a
query.

How can the correct datatype be defined in Access?


If a DataType on the server is one that Access does not have an equiveleant
type for then it will often be recognized as text. Nothing you can do about
it AFAIK.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #2

P: n/a
ro*******@hotmail.com wrote:
In an Access 97 (and 2000) database that has linked tables to Oracle
which is showing some fields as text when they are numbers in Oracle.
In Oracle they are specified to a size e.g. Number(38).
This is causing some issues when trying join different tables in a
query.


This is invariably a result of the ODBC driver interpreting some
specifications of number of digits and/or decimal places such that they
are translated as text.

For example, an Oracle datatype of Number shows as numeric in Access.

However, Number(n,d) where n = number of digits and d = number of
decimal places and Number(n) can be translated as a text datatype when
the specification is Number(16, 2) or Number(16). I haven't
experimented with specifications other than the 16,2 and 16.

What needs to be done in this case when you want to do numeric
calculations, like sum or other sorts of things, is to apply the Val()
function to the field in Access.

In a report, for example, if field MyValue is a Number(16) in Oracle, it
will be text in Access and a control source of =Sum(MyValue) will give
you an error. Instead, use =Sum(Val(MyValue)).
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #3

P: n/a
Tim Marshall wrote:
ro*******@hotmail.com wrote:
In an Access 97 (and 2000) database that has linked tables to Oracle
which is showing some fields as text when they are numbers in Oracle.
In Oracle they are specified to a size e.g. Number(38).
This is causing some issues when trying join different tables in a
query.


I just re-read your last sentence.

If you're using linked tables, as long as the datatypes in Oracle for
the joins are the same (and they should be or someone needs his/her
fingers rapped), you'll be OK - it'll be text joining to text.

Where you'll have problems is if the corresponding join field in another
Oracle table is a Number field whie the field in the other table is
Number(38). You won't be able to do a join on the Access query grid or
even use it, I don't think.

You'll need to go to the SQL view and do the following for the From
clause. In the example, ORD_UNT_FK is Number (Access/Jet Numeric) and
UNT_PK is Number(38) (Access/Jet Text):

FROM TBL_TURN_ORDERS INNER JOIN TBL_UNITS ON TBL_TURN_ORDERS.ORD_UNT_FK
= Val(TBL_UNITS.UNT_PK)

As you can see, I'm putting the Val() function on the UNT_PK.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #4

P: n/a
Tim Marshall wrote:
ro*******@hotmail.com wrote:
In an Access 97 (and 2000) database that has linked tables to Oracle
which is showing some fields as text when they are numbers in Oracle.
In Oracle they are specified to a size e.g. Number(38).
This is causing some issues when trying join different tables in a
query.


ODBC (and Windows in general) does not have a native datatype that will
recognize integer-type numbers 38 digits in length.

Even a NUMBER(16) will fail if the number is greater than or equal to
2^32, because the max positive value for a LONG INTEGER in Access is
(2^32-1).

Nov 13 '05 #5

P: n/a
Tim Marshall wrote:

<snip...>
You'll need to go to the SQL view and do the following for the From
clause. In the example, ORD_UNT_FK is Number (Access/Jet Numeric) and
UNT_PK is Number(38) (Access/Jet Text):

FROM TBL_TURN_ORDERS INNER JOIN TBL_UNITS ON TBL_TURN_ORDERS.ORD_UNT_FK
= Val(TBL_UNITS.UNT_PK)

As you can see, I'm putting the Val() function on the UNT_PK.


Yes, but if UNT_PK has values in it >= 2^32, Val() will throw an error
on them, and the join just will not work as expected. Oh, and the QBE
grid will complain about "unsupported SQL syntax" or something like this
if you try to go back into Design mode, so you're stuck editing the SQL
by hand at this point.

Nov 13 '05 #6

P: n/a
corey lawson wrote:
FROM TBL_TURN_ORDERS INNER JOIN TBL_UNITS ON
TBL_TURN_ORDERS.ORD_UNT_FK = Val(TBL_UNITS.UNT_PK)

As you can see, I'm putting the Val() function on the UNT_PK.
Yes, but if UNT_PK has values in it >= 2^32, Val() will throw an error
on them, and the join just will not work as expected.


Well, that depends on how high your sequences are going. I've
personally never ever run into anything even remotely close to a small
fraction of that number in Oracle, but of course I can't speak for
everyone's experience.

OTOH, that sounds like the same rationalization one heard in the 80s for
a 2 digit date. 8)

Personally, I rarely use linked tables any more anyway - DAO pass
through queries work just fine and I vastly prefer construction of joins
using the theta join syntax in Oracle as compared to the clumsier (in
terms of contructing the string by VBA) ANSI syntax in Access linked tables.
Oh, and the QBE
grid will complain about "unsupported SQL syntax" or something like this
if you try to go back into Design mode, so you're stuck editing the SQL
by hand at this point.


Yes, you'll note that I said "You'll need to go to the SQL view". I'm
not aware of being able to use the query design grid to represent a
function applied to either or both joined fields.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.