Oracle 10g RC2 ODBC Access - odbc failed - where xyz <> "S"

[network-admin]

We have Problems with Access query on Oracle 10g Database with ODBC
Connection.

The Query_1 is such as
select *
from xtable
where ycolumn <"S"

Result = ODBC Faild
----------------------------------------------------
The Query_2
select *
from xtable
where ycolumn = "S"

is working.
The query_1 had worked even last month. But now it doesn't work any
more.
In mdb files where i don't link the oracle table new it just work too.
But we have much passthrough and the down work any more :(

Someone any ideas?

(With MS ORACLE ODBC Driver it works fine - but we have performance
problems with it.
Clients that dont work.

Client 1
Windows Server 2003
Oracle ODBC 9.2

Client 2
Windows XP
Oracle ODBC 10.2.0.1.0

Client 3
Windows XP
Oracle ODBC 10.2.0.3.0

Client 4
Windows XP
Oracle ODBC 10.2.0.2.0

[Gord]

On Mar 17, 7:54 am, "network-admin" <[e-mail]:

We have Problems with Access query on Oracle 10g Database with ODBC
Connection.

The Query_1 is such as
select *
from xtable
where ycolumn <"S"

Result = ODBC Faild
----------------------------------------------------
The Query_2
select *
from xtable
where ycolumn = "S"

is working.

The query_1 had worked even last month. But now it doesn't work any
more.
In mdb files where i don't link the oracle table new it just work too.
But we have much passthrough and the down work any more :(

Someone any ideas?

(With MS ORACLE ODBC Driver it works fine - but we have performance
problems with it.

Clients that dont work.

Client 1
Windows Server 2003
Oracle ODBC 9.2

Client 2
Windows XP
Oracle ODBC 10.2.0.1.0

Client 3
Windows XP
Oracle ODBC 10.2.0.3.0

Client 4
Windows XP
Oracle ODBC 10.2.0.2.0

I dunno, both of the following queries work for me (Oracle ODBC
10.2.0.1)...
1. Select query on linked Oracle table:

SELECT GDT_xtable.*
FROM GDT_xtable
WHERE (((GDT_xtable.ycolumn)<>"S"));
2. Pass-through query using Oracle ODBC:

select * from "xtable" where "ycolumn"<>'S'
Does the error message give you any other information in addition to
"ODBC -- Call failed"?

[network-admin]

No, there is no more error code.

Its really strangely.
Old mdb files work fine until I relink Oracle table or link a new
Table that wasn't link before.

[Gord]

On Mar 17, 12:08 pm, "network-admin" <[e-mail]:

No, there is no more error code.

Its really strangely.
Old mdb files work fine until I relink Oracle table or link a new
Table that wasn't link before.

Try checking the connection information that is saved when Access
links a table. Make a copy of one of the old mdb files where the
linked table still works. Open the database file, hit [Alt-F11] to
enter the VBA editor, hit [Ctrl-G] to open the Immediate Window (if it
isn't open already). Type

?CurrentDB.TableDefs("GDT_xtable").Connect

(substituting your linked table for "GDT_xtable") and hit [Enter].
Access should return a string like this:

ODBC;DSN=Oracle ODBC local
GDT;DBQ=XE ;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T ;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS =T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;

Re-link the table, verify that it doesn't work, then run the above
command again. How are the .Connect strings different, if at all?

BTW, if you're curious as to what those parameters mean, look here

http://download-east.oracle.com/docs...8/app_odbc.htm

[dreadnought8]

You need to get at the Errors object for more detail whenever you get
an ODBC error.
Put this in your code and call it as soon as you trap an error:

Function ExpandedErrorMessage() As String
'Called when an ODBC type error is detected
'Looks through the Errors collection and documents all the errors
encountered
Dim MyErr
If Err.Number <0 Then
ExpandedErrorMessage = ExpandedErrorMessage & Err.Number & " " &
Err.Description & vbCrLf
End If
ExpandedErrorMessage = ExpandedErrorMessage & "Error Object:" & vbCrLf
For Each MyErr In Errors
ExpandedErrorMessage = ExpandedErrorMessage & MyErr.Number & ":" &
MyErr.Description & vbCrLf & " Source:" & MyErr.Source & vbCrLf
Next
End Function

Cheers

Terry Bell

[network-admin]

:(
It is only working on few mdb files and if enter the code "?
CurrentDB.TableDefs("GDT_xtable").Connect"
It says Microsoft Oracle Driver.

[network-admin]

okay i will test it.

I used Microsoft ODBC source trapping
i got following errors

[Oracle][ODBC][Ora]ORA-00942: Tabelle oder View nicht vorhanden
[Oracle][ODBC][Ora]ORA-01722: Ungültige Zahl

But if i use a "normal" sql it works and i don't get ORA-00942 code.
I don't select a number field so i don't understand how i can get
ORA-01722.

[Gord]

>if enter the code

>"?CurrentDB.TableDefs("GDT_xtable").Connect"
It says Microsoft Oracle Driver.

It sounds like the old files used Microsoft's driver. Perhaps your
queries never did work with Oracle's driver.

>But if i use a "normal" sql it works and i don't get ORA-00942 code.

What do you mean by "a 'normal' sql"?

>I don't select a number field so i don't understand how i can get
ORA-01722.

That error is often associated with implicit type conversions. What is
the DDL of the table you are using? Mine was

CREATE TABLE "xtable"
( "ycolumn" VARCHAR2(1),
"zcolumn" VARCHAR2(3)
)
/
On Mar 19, 10:40 am, "network-admin" <[e-mail]:

okay i will test it.

I used Microsoft ODBC source trapping
i got following errors

[Oracle][ODBC][Ora]ORA-00942: Tabelle oder View nicht vorhanden
[Oracle][ODBC][Ora]ORA-01722: Ungültige Zahl

But if i use a "normal" sql it works and i don't get ORA-00942 code.
I don't select a number field so i don't understand how i can get
ORA-01722.

[network-admin]

This i mean with "normal sql".
SELECT
SCHEMA.xtable.yolumn,
SCHEMA.xtable.acolumn
FROM
SCHEMA.FA
WHERE
SCHEMA.xtable.ycolumn = 'S' AND
SCHEMA.xtable.acolumn = 'IAB'

And this is the "problem sql"
SELECT
SCHEMA.xtable.yolumn,
SCHEMA.xtable.acolumn
FROM
SCHEMA.FA
WHERE
SCHEMA.xtable.ycolumn <'S' AND
SCHEMA.xtable.acolumn = 'IAB'

Its a big ddl i just insert the specific field or do you need all ?
CREATE TABLE "schema"."xtable" ( "acolumn" VARCHAR2(4), "num"
NUMBER(7), "FAKT_STATUS" VARCHAR2(1) DEFAULT 'A' ,
CONSTRAINT "KEY_FA" PRIMARY KEY ("acolumn", "num") VALIDATE )
TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 65536K BUFFER_POOL DEFAULT) LOGGING

I wanted to use an workaround, but i just have the same problem when i
use the following sql:
......WHERE
schema.ycolumn in ('R','K','A','B','F','G') AND schema.acolumn = 'IAB'

[Gord]

>Its a big ddl i just insert the specific field or do you need all ?

Try to recreate the error using a simplified table like the DDL you
posted.

Also, I notice that:

1. The DDL you posted does not include "ycolumn".

2. Your queries refer to "FA", which was not previously mentioned. Is
that a view?
On Mar 19, 1:24 pm, "network-admin" <[e-mail]:

This i mean with "normal sql".
SELECT
SCHEMA.xtable.yolumn,
SCHEMA.xtable.acolumn
FROM
SCHEMA.FA
WHERE
SCHEMA.xtable.ycolumn = 'S' AND
SCHEMA.xtable.acolumn = 'IAB'

And this is the "problem sql"
SELECT
SCHEMA.xtable.yolumn,
SCHEMA.xtable.acolumn
FROM
SCHEMA.FA
WHERE
SCHEMA.xtable.ycolumn <'S' AND
SCHEMA.xtable.acolumn = 'IAB'

Its a big ddl i just insert the specific field or do you need all ?
CREATE TABLE "schema"."xtable" ( "acolumn" VARCHAR2(4), "num"
NUMBER(7), "FAKT_STATUS" VARCHAR2(1) DEFAULT 'A' ,
CONSTRAINT "KEY_FA" PRIMARY KEY ("acolumn", "num") VALIDATE )
TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 65536K BUFFER_POOL DEFAULT) LOGGING

I wanted to use an workaround, but i just have the same problem when i
use the following sql:
.....WHERE
schema.ycolumn in ('R','K','A','B','F','G') AND schema.acolumn = 'IAB'

[network-admin]

FAKT_STATUS = ycolumn

FA = xtable

sorry my mistake

[Tim Marshall]

network-admin wrote:

And this is the "problem sql"
SELECT
SCHEMA.xtable.yolumn,
SCHEMA.xtable.acolumn
FROM
SCHEMA.FA
WHERE
SCHEMA.xtable.ycolumn <'S' AND
SCHEMA.xtable.acolumn = 'IAB'

Gord's suggestions on ddl issues notwithstanding, try using =! (or is it
!= - I'm at home right now, away from my Oracle DB).

I know in my particular user community (for a specific Oracle
application) that there were some problems with ODBC connection to
version 10g. I believe (going by memory) that the actual Oracle ODBC
driver had to be replaced with the MS driver!!! We are on 9i right now
and I am positively dreading the eventual move to 10g.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[network-admin]

On 18 Mrz., 06:11, [e-mail] wrote:

You need to get at the Errors object for more detail whenever you get
an ODBC error.
Put this in your code and call it as soon as you trap an error:

Function ExpandedErrorMessage() As String
'Called when an ODBC type error is detected
'Looks through the Errors collection and documents all the errors
encountered
Dim MyErr
If Err.Number <0 Then
ExpandedErrorMessage = ExpandedErrorMessage & Err.Number & " " &
Err.Description & vbCrLf
End If
ExpandedErrorMessage = ExpandedErrorMessage & "Error Object:" & vbCrLf
For Each MyErr In Errors
ExpandedErrorMessage = ExpandedErrorMessage & MyErr.Number & ":" &
MyErr.Description & vbCrLf & " Source:" & MyErr.Source & vbCrLf
Next
End Function

Cheers

Terry Bell

okay i got it :)

3146 odbc aufruf fehlgeschlagen

error object
[oracle][odbc]restricted data type attribute violation
[oracle][odbc]s1c00driver not capable

source odbc.field
3146 odbc aufruf fehlgeschlagen

source dao.field

[network-admin]

On 18 Mrz., 06:11, [e-mail] wrote:

You need to get at the Errors object for more detail whenever you get
an ODBC error.
Put this in your code and call it as soon as you trap an error:

Function ExpandedErrorMessage() As String
'Called when an ODBC type error is detected
'Looks through the Errors collection and documents all the errors
encountered
Dim MyErr
If Err.Number <0 Then
ExpandedErrorMessage = ExpandedErrorMessage & Err.Number & " " &
Err.Description & vbCrLf
End If
ExpandedErrorMessage = ExpandedErrorMessage & "Error Object:" & vbCrLf
For Each MyErr In Errors
ExpandedErrorMessage = ExpandedErrorMessage & MyErr.Number & ":" &
MyErr.Description & vbCrLf & " Source:" & MyErr.Source & vbCrLf
Next
End Function

Cheers

Terry Bell

I got but i don't understand the code

3265 elemnt in der auflistung nicht gefunden

error objects
3265:
element in dieser auflistung nicht gefunden
source: dao.fields

in english: element in the listing not found or better cell in the
listing not found

[network-admin]

okay

mini table works fine.

but odbc connect is different

work:
Leerzeichen
BNF=F;
qto=T ( T implies that query timeout is to be enabled.)
TLO=O (Letter 0)

don't work
QTO=F (F implies that query timeout is to be disabled.)
TLO=0 (Number 0)

i don't find in internet what tlo is!

[Gord]

On Mar 20, 2:33 pm, "network-admin" <[e-mail]:

okay

mini table works fine.

but odbc connect is different

work:
Leerzeichen
BNF=F;
qto=T ( T implies that query timeout is to be enabled.)
TLO=O (Letter 0)

don't work
QTO=F (F implies that query timeout is to be disabled.)
TLO=0 (Number 0)

i don't find in internet what tlo is!

According to...

http://www.oracle.com/technology/sof...cs/ODBCFAQ.pdf

....TLO refers to the "Translation Option". It apparently relates to
National Language Support (NLS). More info here:

http://www.oracle.com/technology/tec...lang%20faq.htm

[network-admin]

I made a mistake - the odbc connect is the same.

With a new table it works :( but with the orginal not.

ddl of the test table

CREATE TABLE "schema"."ARMIN_TEST" ( "ART" VARCHAR2(4), "NR"
NUMBER(7), "STATUS" VARCHAR2(1) DEFAULT 'A' , CONSTRAINT
"KEY_ARMIN_TEST" PRIMARY KEY ("ART", "NR") VALIDATE ) TABLESPACE
"USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K
BUFFER_POOL DEFAULT) LOGGING