Gary Kjos
garykjos at gmail.com
Wed Aug 23 13:34:20 CDT 2006
I have Enable DDE Refresh checked OLE/DDE timeout = 30 Refresh interval = 60 Number of Update Retries = 2 ODBC Refresh Interval Sec = 1500 Update retry interval = 250 Record Level Locking is checked One other thing I didn't mention before is that everything I do via Access/ODBC is read only so in the ODBC Data source setup I do have the Readonly Connection option checked too. Not sure if that is the default. I'm using Access 2003 now but started doing this using Access 2000. We have a combination of 8i and 9i Oracle databases that I connect to. GK On 8/23/06, O'Connor, Patricia (OTDA) <Patricia.O'Connor at otda.state.ny.us> wrote: > Hi Gary, > > Thanks!! > If I run same query in PL_SQL no problem without touching any timers. > Asked our DBA and they checked the ORACLE logon and there were no login > limits. > So we checked the SQLNET.ORA file and the SQLNET.EXPIRE_TIME=0. > I set the registry to 6000. > > Just out of curiosity what do you have in the TOOLS OPTIONS ADVANCED > settings > Enable DDe refresh > OLE/DDE timeout 300 > REfresh interval 60 > Number of update retries 2 > Odbc refresh interval 1500 > Update retry interval 250 > Record level locking > > This is so strange. Will try looking at ms forums maybe something there. > 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 > ************************************************** > > > > > -------------------------------------------------------- > This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. > > > -----Original Message----- > > > From: accessd-bounces at databaseadvisors.com > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gary Kjos > > Sent: Tuesday, August 22, 2006 02:14 PM > > To: Access Developers discussion and problem solving > > Subject: Re: [AccessD] Change ODBC timeout default query property > > > > 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 > > > ************************************************** > > > > > > > > > > > > > -------------------------------------------------------- > > > This e-mail, including any attachments, may be > > confidential, privileged or otherwise legally protected. It > > is intended only for the addressee. If you received this > > e-mail in error or from someone who was not authorized to > > send it to you, do not disseminate, copy or otherwise use > > this e-mail or its attachments. Please notify the sender > > immediately by reply e-mail and delete the e-mail from your system. > > > > > > > > > -----Original Message----- > > > > > > > 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 > > > > > > > -------------------------------------------------------- > > > > > > -- > > > > > > Gary Kjos > > > > > > garykjos at gmail.com > > > > > > -- > > > > Gary Kjos > > > > garykjos at gmail.com > > > > -- > > > -- > > > AccessD mailing list > > > AccessD at databaseadvisors.com > > > http://databaseadvisors.com/mailman/listinfo/accessd > > > Website: http://www.databaseadvisors.com > > > > > > > > > -- > > Gary Kjos > > garykjos at gmail.com > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- Gary Kjos garykjos at gmail.com