ODBC to Oracle (table or view does not exist)

Giganews Newsgroups
Subject: ODBC to Oracle (table or view does not exist)
Posted by:  BillCo (coleman.bi…@gmail.com)
Date: 1 Aug 2006

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?

Replies