473,887 Members | 2,327 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

ODBC to Oracle (table or view does not exist)

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=******;U ID=*******;PWD= ******;"
db.QueryDefs.Ap pend 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?

Aug 1 '06 #1
6 17634
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=******;U ID=*******;PWD= ******;"
db.QueryDefs.Ap pend 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?
Aug 1 '06 #2
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?

Aug 1 '06 #3
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!

Aug 1 '06 #4
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?
Aug 1 '06 #5
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
Aug 1 '06 #6
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=******;U ID=*******;PWD= ******;"
db.QueryDefs.Ap pend 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?

Aug 2 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
22183
by: Alex | last post by:
I'm new to Oracle, so this question may sound silly. I have been given a list of Oracle tables (they may be views, I'm not sure) that are available to me. I can run simple SQL select statements against most of them in SQL Plus. But some of them have "slashes" in their names such as /XYZ/CUSTOMERS or /XYZ/ADDRESSES . When I try to use the name the way it's listed (with slashes) I get an error. I even tried replacing the slashes with dots,...
3
8119
by: valexena | last post by:
Which Oracle dictionary view gives information about the position of a column in a primary key? -- Posted via http://dbforums.com
0
6947
by: Katuil Katuil | last post by:
Hello All: Have a problem that I have never seen before and have not found anything on technet on how to resolve it. I have a SQL Server 2000 server that I have created a new database. I then used DTS to import data from a MS Access 97 database. Before making the import, I made changes to the MS Access database
1
2460
by: Marcus Leon | last post by:
Access freezes when we attempt to link to and then open an Oracle table that has a Timestamp column. Does anyone know why? This issue does not occur if you attempt to link to and open a table that has Date fields. Thanks, Marcus.
8
8478
by: Gerry Abbott | last post by:
Could someone please tell me is there a simple way to refresh a table open in standard table view, without having to either close and re-open, or open in design than back to datasheet view. Many thanks. Gerry Abbott
1
3689
by: Diffident | last post by:
Howdy, How can I set the CacheDependency to an Oracle table? I have come across SqlDependency class for using the CacheDependency to an SQL server table but not Oracle. Any pointers? Thanks!!!
2
5543
by: rcc.gamer | last post by:
Hello All, I am trying to connect a GridView to an Oracle table using the standard SqlDataSource. The process is fairly simple and there are detailed instructions from Microsoft ... http://msdn2.microsoft.com/en-us/library/ms178304(vs.80).aspx The problem is not accessing or displaying data from an Oracle table .... that all works great!
0
1275
by: NasirMunir | last post by:
I am trying to compare records from oracle table with a table in access. With oraDynaset, I have the option of .findNext function. I can use that to look for matching records from access to oracle. My problem is: I have to look up in access table and delete the records from oracle table if a certain record is not available in the access table. Is there a way of doing that ? Can I get some help ? To make my point more clear, here is the code ,...
3
2692
by: rmurgia | last post by:
I created an ODBC connection with the Microsoft ODBC for Oracle driver and successfully connected to Oracle from Microsoft Access by creating a pass through query. Can I use this connection from within SQL Server 2000 to issue a Select statement and pull data from an Oracle table? i.e. Select * from Oracle.dbo.<table Name>
0
9957
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11173
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10770
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10875
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10432
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7988
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7141
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
2
4238
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3245
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.