ODBC connection fails only for a single table

I have Oracle 9.2 tables linked in Access. Data can be pulled from all
the tables except one !! It gives "ODBC connection failed" error when
trying to open the link.I deleted the link and created it again. But
didn't help. Closing and reopening Access didn't work either.
Any ideas?

Thanks in advance.

Nitin wrote:
[color=blue]
> I have Oracle 9.2 tables linked in Access. Data can be pulled from all
> the tables except one !! It gives "ODBC connection failed" error when
> trying to open the link.I deleted the link and created it again. But
> didn't help. Closing and reopening Access didn't work either.
> Any ideas?[/color]

This frequently happens when there are date/time fields in Oracle that
store only time. Possibly some other data types as well, CLOBS, BLOBS,
though I've never worked with these.

It's not an issue in Pass through queries.

The thing to do is make your link. Then, open a select query against
the linked table and bring down one field at a time and run it. This
will at least show you what field(s) are causing it.

Another thing to consider if the above does not work. Does the
offending table have a column/fields with long datatype(only one
column/field of data type long is permitted per table in Oracle)? In
your DSN Administrator, go to configure of the DSN in question. There
are workaround options (for the Oracle driver, not sure about MS), and
making sure "Force retrievalof long columns" is checked can help.

But the first issue I described is the most likely culprit.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Thanks very much Tim for your reply! I will try a select query as per
your advise.
The table does not have any long or LOB columns. It has only char,
number and date columns. The maximum sizes are char(10) and number(12).

By the way I imported the same table in another Oracle database and
tried the link. The link works fine for the other database. So what
causes this problem only for this particular table? I would have
expected the same behaviour on the other database as well.

Thank you.

Nitin wrote:
[color=blue]
> By the way I imported the same table in another Oracle database and
> tried the link. The link works fine for the other database. So what
> causes this problem only for this particular table? I would have
> expected the same behaviour on the other database as well.[/color]

I'm not sure. Sometimes it's a specific record that causes it. Is it
the very same data and the very same structure? Are you using the same
DSN? If not, what drivers are the two DSNs using? Are the databases on
the same service? The same server?

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Yes, it's the very same data and structure. I am using a different DSN
as the other database lies on a different server. I use Oracle ODBC
driver.
As per your suggestion, I wrote a query against the linked table. I
tried selecting one column at a time. But every time the query failed.
:(

Nitin wrote:
[color=blue]
> Yes, it's the very same data and structure. I am using a different DSN
> as the other database lies on a different server. I use Oracle ODBC
> driver.
> As per your suggestion, I wrote a query against the linked table. I
> tried selecting one column at a time. But every time the query failed.
> :([/color]

That's pretty much stumped me. I would try creating a different DSN and
see what happens then. If that doesn't work, I'd try creating a
passthrough query:

Select * from Tbl_name

and see how that works.

If the pass through query doesn't work, then it's time to check with
your Oracle DBA and see if you can figure things out from there.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Nitin wrote:[color=blue]
> I have Oracle 9.2 tables linked in Access. Data can be pulled from all
> the tables except one !! It gives "ODBC connection failed" error when
> trying to open the link.I deleted the link and created it again. But
> didn't help. Closing and reopening Access didn't work either.
> Any ideas?
>
> Thanks in advance.[/color]

Is the Oracle table really a view that needs to be recompiled?

Hi DFS,
It is a base table, not a view.

Thanks.

I created a new DSN using "Microsoft ODBC for Oracle" driver. It
worked!
Don't know why Oracle driver failed.

Thank you very much for your help!

Nitin wrote:
[color=blue]
> I created a new DSN using "Microsoft ODBC for Oracle" driver. It
> worked!
> Don't know why Oracle driver failed.[/color]

I currently use the Oracle driver and we are still using 8i on our
server. However, I do recall discussions on an specialized application
user group, some discussion about the MS driver working better, but I
thought that might have been for version 10 or greater.

Out of curiousity:

Did you try a new DSN with the Oracle driver? What version of the
Oracle driver are you using? In the past I've seen older versions of
the driver work just fine with newer server installations and then just
freak out one release later...
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me