Linking Access and Oracle problem


J

jlint3024

I am linking an Oracle 9i table to MS Access 2002. I am using an
ODBC connection to Oracle using the Oracle Driver. On one of the
Oracle tables there is key data that is defined in Oracle as
number(38) Access see this data as TEXT. When I go to create an
Access query joining this column and another linked Oracle table whose
data type is just a number - seem by Access as NUMBER I get a Type
Mismatch I the join.

Is there some way I can get this join to work?

Thanks, Jim
 
Ad

Advertisements

D

dbahooker

Access MDB is obsolete; as is Oracle

move to a real database-- like SQL Server 2000 and Access Data
Proejcts
 
D

dbahooker

use an ETL tool in order to import the DATA that you need and then
throw away the Oracle database and the half-dozen DBAs that the
typcial mid-sized company needs to support Oracle

it's called DEAD WEIGHT kid
 
B

Brendan Reynolds

I am linking an Oracle 9i table to MS Access 2002. I am using an
ODBC connection to Oracle using the Oracle Driver. On one of the
Oracle tables there is key data that is defined in Oracle as
number(38) Access see this data as TEXT. When I go to create an
Access query joining this column and another linked Oracle table whose
data type is just a number - seem by Access as NUMBER I get a Type
Mismatch I the join.

Is there some way I can get this join to work?

Thanks, Jim


You'll need to convert the second field to text in the query ...

SELECT FirstTable.SomeField, SecondTable.SomeField FROM FirstTable INNER
JOIN SecondTable ON FirstTable.FieldSeenAsText =
CStr(SecondTable.FieldSeenAsNumber)
 
Ad

Advertisements

D

dbahooker

and of course, you can't do that in design view right?

so you've go to take your pretty query and go VIEW, SQL VIEW which
will then mean that you can't do it in design view ever again right?

Access MDB doesn't support wierd joins in design view; and access data
projects don't have flakiness like this

I can do a cstr-type activity in design view in ADP; but you guys
can't even change SQL and change the design view AT THE SAME TIME

I gwet them both on one screen; you guys have to play in design view;
and then once you do soemthing like a CSTR on a join then you'll
always be stuck in SQL View.

Access is called a friggin flaky database for a reason

move to SQL Server

Access MDB + Oracle = 2 obsolete database too many
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top