1

When using SQL pass-thru queries in MS Access, there is a default time-out of 60 seconds, at which point an instruction is sent to the remote server to cancel the request. Is there anyway to send this command from the keyboard similar to Access' own "Ctrl + Break" operation?

Matt Donnan
  • 4,933
  • 3
  • 20
  • 32

1 Answers1

2

Firstly, understanding how Control-C cancels execution. They probably trap that key sequence, and do something special. I strongly suspect that oracle's client apps (SQL*Plus et al) are calling OCIBreak() behind the scenes, and passing in the handle to the server that they obtained when they executed the query with a previous OCI call.

I also suspect that Access isn't doing anything actively after 60 seconds; that's just the timeout it requests at time of execution query. Even more so, I'm beginning to wonder if Access is even requesting that timeout; everything I've read says that the ODBC driver does not support a query timeout, which makes me think it's just a client-side timeout, but I digress...

So - back to this OCIBreak() call. Here's the bad news: I don't think ODBC implements these calls. To be 100% sure, you'd have to take a look at the ODBC driver for oracle sources, but everything I've read indicates that the API call is not exposed.

For reference, I've been googling with these search terms in combination with "OBDC":

ORA-01013  (error when a user cancelled an operation, or when an operation times out)
OCIBreak   (OCI function which cancels a pending operation)

--- EDIT #1 ---

As a side note, I really believe that Access is just giving up, and not sending any type of cancel command when the Pass-Through timeout is exceeded. If you take a look at this kb article, the ODBC Driver doesn't even support a query timeout:

PRB: Connection Timeout and Query Timeout Not Supported with Microsoft Oracle ODBC Driver and OLE DB Provider

After the elapsed time, Access probably just stops listening for results. If you were to ask oracle for a list of queries that are still executing, I strongly suspect you'd still see yours listed.

--- EDIT #2 ---

As far as implementing your own "cancel" -- which isn't really a cancel, more of a "keep the UI responsive regardless of the state of a query" -- the keyword here is going be asynchronous. You're going to want to rewrite your code to execute asynchronously so that it isn't blocking the message pump for your UI. I'd start googling for "async query access" and see what pops up. One SO result came up:

Running asynchronous query in MS Access

as well as a decent starting point at xtremevbtalk.com:

http://www.xtremevbtalk.com/showthread.php?t=82631

In effect, instead of firing off code that blocks execution until either a timeout occurs or a result set is returned, you'll be asking access to kick off the code behind the scenes. You'll then set up an event that fires when something further happens, such as letting the user know that the timeout occurred (timeout failure), populating a grid with results (success), etc...)

Community
  • 1
  • 1
Lynn Crumbling
  • 12,985
  • 8
  • 57
  • 95
  • Lynn, I also think that the timeout is client side rather than server, what I'm trying to pin down is how exactly after the default 60 secs, Access performs the termination of the query, or if you say does not actively do this whether it sets something at the initial run to kill after 60 secs? – Matt Donnan Mar 06 '12 at 09:40
  • @MattDonnan The pass-through query property sheet has a default ODBC time out of 60. – Fionnuala Mar 06 '12 at 15:19
  • @Remou That's my query, how does this 60 second timeout then actually perform the cancellation of the pass-thru query, as I'm hoping to replicate it from a keyboard shortcut? – Matt Donnan Mar 06 '12 at 15:22
  • @MattDonnan I don't think timeout is supported anyway. See update to answer. – Lynn Crumbling Mar 06 '12 at 16:03
  • @LynnCrumbling That's an interesting point, it hadn't occureed to me that Oracle would still be executing the query after Access had run away. I guess that shifts my question to "How can I make Access, give up at a moment of my choosing?" – Matt Donnan Mar 06 '12 at 16:10
  • I was going to mess with [this](http://support.microsoft.com/kb/142925) and see if it could be got to work with ADO and SQL server because I see that the ADODB Recordset has a Cancel property, but it sounds like it would not help, even if it could be got to work. I will leave it up to you. – Fionnuala Mar 06 '12 at 16:20
  • @MattDonnan Are you using ADO or (god help you) DAO? – Lynn Crumbling Mar 06 '12 at 18:48
  • @MattDonnan Updated answer again. – Lynn Crumbling Mar 06 '12 at 19:05
  • @LynnCrumbling & Remou, Cheers for the suggestions, I had looked a little bit at the ADO method, but unfortunately in this case, my queries are not run through VB, in essence I have a number of stored pass-thru queries, I've set all the timeouts to zero as some take a while to execute and are used in varying automated processes, sometimes I may accidentally open a view of one of my pass-thru's which takes a while by mistake. I then must wait for it to finish or Ctrl+Alt+Del out of Access, so was simply looking to emulate Access' Ctrl+Break function for Access' own queries? – Matt Donnan Mar 06 '12 at 19:24
  • @LynnCrumbling From all the searching around it does look as though what I'm referring to can't be done, however your answer is packed with good information on how to go about this in different ways, thanks : ) – Matt Donnan Mar 13 '12 at 08:59