MartyConnelly
martyconnelly at shaw.ca
Tue Aug 22 15:20:34 CDT 2006
Here are some other set able timeouts, whether they override the registry
settings I don't know. Depends if you are using DAO or ADO.
You might want to check the Oracle Client .ora file too.
Sub querytest()
Dim dbs As Database
Set dbs = CurrentDb
Debug.Print dbs.QueryTimeout 'default 60 secs
dbs.QueryTimeout = 600
Debug.Print dbs.QueryTimeout 'default now 600 secs
Debug.Print CurrentProject.Connection.CommandTimeout '30 seconds
' commandtimeout similar to querytimeout
'this is ADODB.Command.CommandTimeout for entire project
Debug.Print CurrentProject.Connection.ConnectionTimeout '15 seconds
Dim LoggedOnQry As QueryDef
Set LoggedOnQry = dbs.QueryDefs("query1")
Debug.Print LoggedOnQry.ODBCTimeout 'default 60 idle timeout?
Dim conn As New ADODB.Connection
conn.Open "Driver={SQL
Server};Server=MARTIN;Database=pubs;Trusted_Connection=yes"
Debug.Print conn.CommandTimeout
Debug.Print conn.ConnectionTimeout
conn.Close
Debug.Print "close"
End Sub
Gary Kjos wrote:
>Hi Patricia,
>
>Weird. Maybe I'm just lucky? ;-)
>
>I''ve got some extract queries that run for a few hours before
>returning any results so I'd really be in trouble if it didn't work as
>it does for me.
>
>Perhaps you could test the database login theory if you ran a similar
>query directly against the Oracle Database using another tool such as
>SQL Navigator, Toad or SQL Plus, taking the ODBC driver out of the
>mix. If the query works there then it isn't default limits on the
>login causing your problem. Or if you are on friendly enough terms
>with your Oracle DBA you could ask them what they think is going on.
>
>Good luck figuring it out and be sue and let us know when you do.
>
>GK
>
>On 8/22/06, O'Connor, Patricia (OTDA)
><Patricia.O'Connor at otda.state.ny.us> wrote:
>
>
>>Hi Gary
>>I use the same ODBC driver and even tried rebooting - still get same
>>message. Will check into the Oracle Database login to see if they have
>>something set up there.
>>
>>I recommend the oracle odbc over the microsoft because the date handling
>>is better. Think it is better for passthru's also. There was something
>>else related also but I can't remember at just this moment.
>>
>>Thanks
>>
>>**************************************************
>>* Patricia O'Connor
>>* Associate Computer Programmer Analyst
>>* OTDA - BDMA
>>* (W) mailto:Patricia.O'Connor at otda.state.ny.us
>>* (w) mailto:aa1160 at nysemail.state.ny.us
>>**************************************************
>>
>>
>>
>>>From: accessd-bounces at databaseadvisors.com
>>>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gary Kjos
>>>Sent: Tuesday, August 22, 2006 12:07 PM
>>>To: Access Developers discussion and problem solving
>>>Subject: Re: [AccessD] Change ODBC timeout default query property
>>>
>>>I am currently using Oracle ODBC driver 9.02.00.54 Yep.
>>>unclicking the "Enable Query Timeout" box does the trick for
>>>me and my users.
>>>That is the only option we change from default when setting
>>>up the ODBC data source. We use System DSN's for all ODBC
>>>definitions. I wonder if your Oracle Database login has a
>>>defaulting timeout level that is biting you..
>>>
>>>I have also used the Microsoft ODBC driver with good results.
>>>The only issue there was that if the Oracle Database login
>>>password was expired the Microsoft ODBC driver didn't prompt
>>>for entering a new password as the Oracle one did. that may
>>>have changed with a newer Microsoft driver though. We used to
>>>use a generic login and password for everyone's Access ODBC
>>>into Oracle stuff, but because of Sarbains/Oxley we were
>>>required to change to individual user logins that had
>>>expiring passwords. So that was when the Microsoft ODBC
>>>driver not prompting for new password became more of an issue.
>>>
>>>On 8/22/06, O'Connor, Patricia (OTDA)
>>><Patricia.O'Connor at otda.state.ny.us> wrote:
>>>
>>>
>>>>Gary
>>>>
>>>>Which driver do you use? And unclick works?
>>>>
>>>>We are using the ORACLE ODBC driver Ora 9i. But also
>>>>
>>>>
>>>happens against
>>>
>>>
>>>>FOXPRO.
>>>>
>>>>I went to the Administrative tools, Data Sources(ODBC) and
>>>>
>>>>
>>>unclicked
>>>
>>>
>>>>Query Time out. Opened the MDB and tried a pretty simple
>>>>
>>>>
>>>query without
>>>
>>>
>>>>changing the Query Property Timeout over 60. It still timed out.
>>>>
>>>>I get the following message if I don't set the QUERY PROPERTY ODBC
>>>>Timeout from 60 up to at least 500. I routinely set it at 6000.
>>>>
>>>> ODBC--Call failed.
>>>> [Oracle] [ODBC] [ORA] ORA-01013: user requested cancel of
>>>>current operation (#1013)
>>>>
>>>>I know immediately that I forgot to update the timeout by
>>>>
>>>>
>>>the message
>>>
>>>
>>>>above. Many others do not understand so I am always
>>>>
>>>>
>>>checking out their
>>>
>>>
>>>>queries and machines.
>>>>
>>>>I was wondering whether there was a change to Registry or
>>>>
>>>>
>>>some hidden
>>>
>>>
>>>>MS change that would fix this.
>>>>
>>>>Thanks
>>>>**************************************************
>>>>* Patricia O'Connor
>>>>* Associate Computer Programmer Analyst
>>>>* OTDA - BDMA
>>>>* (W) mailto:Patricia.O'Connor at otda.state.ny.us
>>>>* (w) mailto:aa1160 at nysemail.state.ny.us
>>>>**************************************************
>>>>
>>>>-----Original Message-----
>>>>
>>>>
>>>>
>>>>>From: accessd-bounces at databaseadvisors.com
>>>>>[mailto:accessd-bounces at databaseadvisors.com] On Behalf
>>>>>
>>>>>
>>>Of Gary Kjos
>>>
>>>
>>>>>Sent: Tuesday, August 22, 2006 10:11 AM
>>>>>To: Access Developers discussion and problem solving
>>>>>Subject: Re: [AccessD] Change ODBC timeout default query property
>>>>>
>>>>>Which ODBC driver are you using? With the Oracle ODBC Driver for
>>>>>Oracle you can tell it to just NOT timeout by unchecking
>>>>>
>>>>>
>>>the Timeout
>>>
>>>
>>>>>checkbox.
>>>>>
>>>>>On 8/22/06, O'Connor, Patricia (OTDA)
>>>>><Patricia.O'Connor at otda.state.ny.us> wrote:
>>>>>
>>>>>
>>>>>>Hi all
>>>>>>
>>>>>>Is there a way to set the ODBC timeout default in the query
>>>>>>properties from 60 to 6000 permanently? Most of our work
>>>>>>
>>>>>>
>>>>>is against
>>>>>
>>>>>
>>>>>>our ORACLE databases, some Foxpro, and SqlServer. For every
>>>>>>
>>>>>>
>>>>>new query
>>>>>
>>>>>
>>>>>>we do we have to remember to reset this before running
>>>>>>
>>>>>>
>>>>>otherwise we get a timeout.
>>>>>
>>>>>
>>>>>>Does not matter how easy or hard the query is, If it isn't at
>>>>>>least 600 then poof it stops. One person said that it would
>>>>>>
>>>>>>
>>>>>cause their MDB
>>>>>
>>>>>
>>>>>>to corrupt but I haven't had that problem.
>>>>>>
>>>>>>The OPTIONS ADVANCED does not do this. I have looked almost
>>>>>>everywhere but can't find out how to change the default.
>>>>>>
>>>>>>
>>>>>This would
>>>>>
>>>>>
>>>>>>be for 2k or 2k3.
>>>>>>Any help appreciated
>>>>>>
>>>>>>Thanks
>>>>>>
>>>>>>**************************************************
>>>>>>* Patricia O'Connor
>>>>>>--------------------------------------------------------
>>>>>>
>>>>>>
>>>>>
>
>
>
--
Marty Connelly
Victoria, B.C.
Canada