ODBC to Oracle (table or view does not exist)

[BillCo]

I've reached the limit of my knowledge here and I'm starting to go mad
- any help would be greatfully recieved!!!!

I'm having a strange problem with making pass through queries to an
oracle db with a permament connection string.

- if i create the passthrough query by writing the sql into the sql
builder window and selecting the connection details on opening the
query then everything works fine

- if i run the query in SQL*Plus with the same login details everything
works

- if i create an ado rst using the same connection details it works
fine

However if i create the query and specify the connection string - it
fails:
ODBC--call failed.
[Oracle][ODBC][Ora]ORA-00942: table or view does not exist
(#942)

opening the query in design mode and replacing the sql with "SELECT *
FROM ALL_TABLES" (i.e. same connection string, but different sql)
connects and runs, but only reveals 33 tables - none of which are the
ones I need to connect to.

Here's the sql used to build queries (p'words etc removed):

qDef.Connect = "ODBC;DSN=*******;DBQ=******;UID=*******;PWD=***** *;"
db.QueryDefs.Append qDef

Even weirder still - if i used the linked table ODBC wizard, I can link
to the table - but when I attempt to open it I get the same message. It
appears even Access itself is not getting the connection string
correct.

I'm only new to this oracle thing ...does anyone have any idea what's
going on here?

[Jay]

ODBC driver version match version of Oracle db?

BillCo wrote:

I've reached the limit of my knowledge here and I'm starting to go mad
- any help would be greatfully recieved!!!!

I'm having a strange problem with making pass through queries to an
oracle db with a permament connection string.

- if i create the passthrough query by writing the sql into the sql
builder window and selecting the connection details on opening the
query then everything works fine

- if i run the query in SQL*Plus with the same login details everything
works

- if i create an ado rst using the same connection details it works
fine

However if i create the query and specify the connection string - it
fails:
ODBC--call failed.
[Oracle][ODBC][Ora]ORA-00942: table or view does not exist
(#942)

opening the query in design mode and replacing the sql with "SELECT *
FROM ALL_TABLES" (i.e. same connection string, but different sql)
connects and runs, but only reveals 33 tables - none of which are the
ones I need to connect to.

Here's the sql used to build queries (p'words etc removed):

qDef.Connect = "ODBC;DSN=*******;DBQ=******;UID=*******;PWD=***** *;"
db.QueryDefs.Append qDef

Even weirder still - if i used the linked table ODBC wizard, I can link
to the table - but when I attempt to open it I get the same message. It
appears even Access itself is not getting the connection string
correct.

I'm only new to this oracle thing ...does anyone have any idea what's
going on here?

[BillCo]

Jay wrote:

ODBC driver version match version of Oracle db?

Thanks Jay,
Oracle version is : Oracle9i Release 9.2.0.7.0
How do I make sure the ODBC driver version matches?

[BillCo]

Never mind - I've figured it out!

Turns out I was using a "System Type" DSN that was prefefined on the
computer - I created a user type DSN and used that and Bob's you uncle!

[Jay]

I'm new to Oracle as well, but it's worth a shot to get the latest
driver.

You can see your current driver by going to:
Control Panel/ Administrative Tools/ Data Sources (ODBC)/ Drivers tab

You can find the latest driver at the following link:
http://www.oracle.com/technology/sof...dbc/index.html
BillCo wrote:

Jay wrote:

ODBC driver version match version of Oracle db?

Thanks Jay,
Oracle version is : Oracle9i Release 9.2.0.7.0
How do I make sure the ODBC driver version matches?

[Tim Marshall]

Jay wrote:

You can find the latest driver at the following link:
http://www.oracle.com/technology/sof...dbc/index.html

This is a good link and the whole OTN Oracle site is excellent. You'll
find the folks on the Oracle equivalent(s) of cdma to be a little more
dour and far ready to jump on people for posting something that they
feel should be googled, but you can get great help on the
comp.databases.oracle.* hierarchy.

One thing, though, is it's not necessary to always upgrade the ODBC
driver. I'm using the 8i driver on 9i server and I have people using
drivers from 7.3.4 (nearly fossilized, it's so old) on 9i. Unlike
Access, Oracle is wonderfully upward compatible.

On drivers, some other clients I know of who use an Oracle database
engine in their copies of the main software app (not mine) I'm running
have experienced problems with 10g server and ODBC drivers. I think
with translating long datatypes, but I'm not sure. In their case, their
problems were resolved using the MS Oracle ODBC driver (!).
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[DFS]

Try prefacing the table names in the pass-thru SQL with the schema name:

SELECT Fields
FROM schema.table
WHERE...


BillCo wrote:

I've reached the limit of my knowledge here and I'm starting to go mad
- any help would be greatfully recieved!!!!

I'm having a strange problem with making pass through queries to an
oracle db with a permament connection string.

- if i create the passthrough query by writing the sql into the sql
builder window and selecting the connection details on opening the
query then everything works fine

- if i run the query in SQL*Plus with the same login details
everything works

- if i create an ado rst using the same connection details it works
fine

However if i create the query and specify the connection string - it
fails:
ODBC--call failed.
[Oracle][ODBC][Ora]ORA-00942: table or view does not exist
(#942)

opening the query in design mode and replacing the sql with "SELECT *
FROM ALL_TABLES" (i.e. same connection string, but different sql)
connects and runs, but only reveals 33 tables - none of which are the
ones I need to connect to.

Here's the sql used to build queries (p'words etc removed):

qDef.Connect = "ODBC;DSN=*******;DBQ=******;UID=*******;PWD=***** *;"
db.QueryDefs.Append qDef

Even weirder still - if i used the linked table ODBC wizard, I can
link to the table - but when I attempt to open it I get the same
message. It appears even Access itself is not getting the connection
string correct.

I'm only new to this oracle thing ...does anyone have any idea what's
going on here?