ODBC Timeout 2

[macksta]

Hey there,
I am using Access 2000 connecting to an ORACLE db via the oracle ODBC connection. There is a trigger on table A that does a lot of processing, after one minute access times out and i get the following message:

ORA-01013: User requested cancel of current operation.

I can make the change in TOAD (an ORACLE development tool) with out a problem so i know that it is not the PL/SQL code it is a setting somewhere in ACCESS. I have played with the ODBC settings for hours and cant figure it out.

Any ideas has any one had this problem before.

Thanks in advance,

Jamie Mack.

 

[cmmrfrds]

Can you paste in the connection string and the sql statement?

 

[macksta]

It is updatable via a form that has a recordsource from a linked table (ORACLE ODBC connection v.8.00.05.00). So there is no connection string or SQL statment.

If i goto tables and open up the linked Table A and try to modify the data then the same error occurs.

Cheer Jamie

 

[cmmrfrds]

It looks like you have it narrowed down to the odbc driver. Sorry, I don't use Oracle. Just for your information, there is an Oracle OLEDB Provider, if you wanted to setup a connection and read the table through vba code using ADO connection and recordset objects.

 

[hayo]

I had problems with connection to Oracle some time ago.
Then I found that there are parameters for ODBC which may cause interuption of connection
(depends on your ODBC version):

HKLM\Software\Microsoft\Jet\4.0\Engines\ODBC

Values "QueryTimeout" and "ConnectionTimeout".

Try to set them to 0 ( which means that there is NO time limit at all ).

Hope it helps.

Hayo

 

[Steve101]

It depends on what you are doing, but if its possible, I suggest that you use a passthrough query to manipulate your data on Oracle. This allows all of the database processing to occur on the Oracle server, and prevents the otherwise potentially heavy associated network traffic.

To do this, create an associated passthrough Query definition and save it. To do this, go to your Query design window, create a new query, and select the Query, SQL Specific, Passthrough options. Then type in the Oracle SQL which will perform the action required.

In the query properties, set the ODBC Connection String, ReturnRecords, and ODBCTimeout properties. If you're performing an action (eg. insert, update, delete), then ReturnRecords will be No. If its a standard select, then ReturnRecords will be Yes. Set ODBCTimeOut to zero to prevent a timeout problem.

Save your passthrough Query, then test it, to make sure it works.

Once you are happy that it works, then you can programatically change the SQL string without effecting the other properties. Some sample code to do this follows:

Dim DB as Database: set DB = CurrentDB
Dim QD as QueryDef
Set QD = DB.QueryDefs("qryYourPassthroughQryName&quot
QD.SQL = "SELECT * FROM xyz "
QD.Close
DB.Close

Replace the above SQL String with any legitimate serverside SQL; My oracle is not too good, so I wont hazard a real example.

After the above code executes, the SQL in the saved query will have been set according to the code. To activate it programatically:

docmd.SetWarnings False
Docmd.OpenQuery "qryYourPassthroughQryName"
docmd.SetWarnings True

the first SetWarning line prevent warning you that existing records will be affected by an action query.

That should hopefully do the trick.

Finally, remember, that if you are accessing an industrial strength server database like Oracle or SQL Server, you absolutely should be using passthrough queries, especially if the database is large, and/or the network has a lot of traffic. Otherwise, all the processing will be occuring on the client pc over the network; resulting in inefficient use of your PC, the network AND the database server.

Well, after all of that, I do hope that I have helped to solve your problem,
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[macksta]

Cheers Guys,
I havnt tried out the pass through query but I changed the registry setting and it stopped it timing out.

Cheers

Jamie Mack

 

[Steve101]

Well done Jamie. How about telling us what you did then to fix the problem. It might help someone else.
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[macksta]

To fix this timeout issue I ran "regedit" (start-->run type "regedit&quot

Then updated the registry keys
HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\ODBC"QueryTimeout" and "ConnectionTimeout", to 0 and this stopped the update of the record from timing out.

Cheers Jamie Mack.

 

[Steve101]

Thanks Jamie; here's a star for that info, which has now been recorded and might help someone else. I reckon you might want to 'reward' hayo in the same way for pointing you in the right direction.
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[jmeadows7]

The original post was for a trigger - if you needed to call a stored procedure and set the time-out programatically so it benefits all users of the app and not have to change the registry on all computers - the following works with invoked a sp in SQL Server:

strSQL = "exec cssp008_Update_Planer_Prod_Data"
qdf.ReturnsRecords = False
qdf.SQL = strSQL
dbs.QueryTimeout = 2000 'Set timeout to 4 minutes so it doesn't timeout.
qdf.ODBCTimeout = 1000
qdf.Execute
DoCmd.Hourglass False
DoCmd.Beep