Re: ODBC to Oracle (table or view does not exist)

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?

.

Relevant Pages

  • Re: Pass Through Query Question
    ... "Joe Williams" wrote in message ... > when you mentioned setting the connection string in the properties of the ... It runs with no problem using SQL query anlayzer. ...
    (microsoft.public.access.queries)
  • Re: SELECT DISTINCT slow, how can I speed up
    ... We have staff using Access 2003 as a front end to the Oracle tables ... for the purposes of ad hoc queries. ... come back within a second of the SQL starting to run. ... only 20-50 rows come back and the query takes 28 seconds and there is ...
    (comp.databases.oracle.server)
  • Re: SQL -> Oracle
    ... > 2- extracted all my SQL queries in one single file (that was the tedious ... > 3- now whenever I need new query. ... > - Sybase and SQL server prefix their parameters with '@' ... > - Firebird and Oracle have selectable stored procedures so you use them ...
    (borland.public.delphi.non-technical)
  • Re: SQL query fails
    ... You can't use the same wild card character in all db engines. ... character" wild card in MS SQL & Oracle is the underline; ... > I have a query that works in Oracle and SQL Server, ...
    (microsoft.public.access.queries)
  • Re: ORA-1866 The datetime class is invalid error...the saga continues!
    ... but you could try using a different data provider. ... Use the Microsoft ADO.NET Oracle Provider ... VB.NET to my employers and not being able to query data using a date field ... SQL statements I provided earlier will execute flawlessly in any enviornment ...
    (microsoft.public.dotnet.general)