[ORacle][ODBC][Ora]ORA-01013 error

[williey]

Using MS Access 2000 to link an Oracle view.

The following error is received when I try to run the query. I read somewhere the ODBC Timeout field can to be changed to increase the wait time. But I can't find the "ODBCTimeout" property in the Properties tab.

ODBC --call failed.
[ORacle][ODBC][Ora]ORA-01013: user requested cancel of current operation (#1013)

 

[genomon]

You have to create an instance of the connection object within the workspace object, then use this method
where conWork is the connection:
conWork.QueryTimeout = 0

Good Luck!
-Geno

The Microsoft mascot is a butterfly.
A butterfly is a bug.
Think about it....

 

[williey]

Geno,
How do you do that? All I have setup is the Tools->Database Utilities->Linked Manager. I have created a link to the Oracle table/view.
When I try to open the linked table/view, it failed with the ORA-01013 error code. Do I need to code the ODBC Timeout property somewhere?

 

[PCLewis]

When you linked the table, did it prompt you to select the index fields? Wa sit a VIEW or TABLE you linked to?

The ODBC timeout thing you refer to is a system wide one and I recall reading about it too but I never changed it. It is in the registry (somewhere). The first hit on google using words "registry odbc timeout" was:

http://support.microsoft.com/kb/282349

Which should help. However I think your problem is not related to the ODBC timeout unless you know the Oracle server is incredibly busy...

 

[jsteph]

What Oracle ODBC driver are you using? The Microsoft one appears faster for linked tables and views, while the Oracle one appears much, much slower.

I say 'appears', because one driver may be coded to use the 'fast first rows' method when it simply opens a linked table in datasheet view.

This will fetch the first 100 or so rows doing a full table scan and return them. This makes the initial opening appear snappy, but that's mainly because it just started returning the first rows it found at the beginning of the physical table. Other drivers may be coded to gather a larger result set on the server, and not start sending you rows until that result set is finished.

It's sort of like the performance difference between the DAO Dynaset and Snapshot--the Dynaset appears to open the recordset much, much faster, but it's actually much slower looping through it (partly because it is updateable), but on opening it just goes to the first record and you're ready to read it, while the snapshot loads the entire thing before it is ready to loop.

You may find it's faster to use ADO recordsets or Pass-thru queries rather than linked tables or views, but I'm not sure the context of where you're using the linked view.
--Jim

 

[williey]

I have changed both the setting in registry and the Oracle refresh interval. The query to the linked view is still timing out..

Next I change the ODBC driver from Oracle to Microsoft and the results. I know OLE is much faster the ODBC. Can you use OLE instead of ODBC to connect to Oracle using Access?

 

[PHV]

Have a look here:

http://www.connectionstrings.com/

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886