Solved: ORACLE Timeouts for VBA scripts

[Tom]

(Oracle 9i and Access 2003)
How can you extend the length of your VBA script time before you get that "user requested" timeout error?

[stanl]

(Oracle 9i and Access 2003)
How can you extend the length of your VBA script time before you get that "user requested" timeout error?

Could you explain this more' I've heard of connection timeouts and recordset fetching timeouts, but not user requested. Stan

[andrew93]

Hi

I'm not familiar with Oracle but to insert a delay into your code, add the following declaration at the top of your code (in the general declarations section) :

Private Declare Sub Sleep Lib "kernel32" _
(ByVal dwMilliseconds As Long)

And then you can use this command to insert a 1 second pause in your code:


Sleep 1000

The API works in milliseconds so to get a 2 second delay use 2000 inplace of 1000, or for half a second use 500 in place of 1000 etc.

Although I suspect from Stan's post introducing a delay may not solve the problem.....

HTH, Andrew

[Tom]

This is the info I get:
Error:ORA-01013: user requested cancel of current operationCause:You interrupted an Oracle operation by pressing Ctrl-C or by canceling the operation through another method (ie: front-end application).Action:The options to resolve this Oracle error are:
The current operation has been terminated by the user. There is no course of action to take. This message is informational only.
This error may also have been caused by a timeout in a sql pass-through query. To resolve this error, properties of the timeout parameter need to be changed to a higher value.

[Tom]

The problem is, I know how to set the ODBC timeout to 0, but that's in queries, not VBA.

[stanl]

The problem is, I know how to set the ODBC timeout to 0, but that's in queries, not VBA.

try using the newest Oracle OLEDB drivers, or use a DSN from Access and set the query timeout there.

[Tom]

GREAT IDEA.....I went to the ODBC Driver Configuration and unchecked the "Enable Query Timeout" and I've just run some programs that have been giving me headaches and they ran GREAT!!!!!!
Thanks, stanl.....I owe you a cup of coffee!

[stanl]

Thanks, stanl.....I owe you a cup of coffee!

Certainly welcome... I seem to be having a good week here:friends: