Asynchronous execution turns synchronous when attempting Cancel

[Jamie Collins]

Consider the following code with executes a query asynchronously and
fetches its resultset asynchronously:

'---<Class 1>---
Option Explicit
Private WithEvents m_rs As ADODB.Recordset

Private Sub Class_Initialize()
Dim sql As String
sql = "<<Query goes here>>"
Set m_rs = New ADODB.Recordset
With m_rs
..ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<<file location goes here??"
..Source = sql
..Open , , , , adAsyncExecute + adAsyncFetch
End With
End Sub

Public Sub Cancel()
'If m_rs.State = adStateFetching Then
m_rs.Cancel
'End If
End Sub
'---</Class 1>---

'---<Module1>---
Option Explicit
Sub main()
Dim c1 As Class1
Set c1 = New Class1
Stop
c1.Cancel
End Sub
'---</Module1>---

The Stop is to pause the code, to prevent the execution being
cancelled before it has begun (i.e. paranoia <g>).

Replace "<<Query goes here>>" with a query (i.e. SQL that returns a
resultset) that takes long time to *execute*, as distinct from a query
that takes a long time to *fetch* the resultset.

When the code hits m_rs.Cancel it seems to become synchronous i.e. it
will cancel but only when the query has completed executing, which
could be a long wait! Same when setting the recordset to Nothing, same
when testing its State property. Using m_rs.ActiveConnection.Cancel
seems to have no effect.

Is this a limitation of the Access/Jet technology (OLE DB provider,
engine)? Cancelling works fine with SQL Server. Any workarounds?

Jamie.

--

 

[Albert D. Kallal]

jet is not a server, nor is it even another process thread.

So, JET is synchronous. The processing, and code that is running is strictly
your single thread of code. This is not difference then opening a text file
and reading it line by line.

If you really do need asynchronous operations, then you can continue to use
ms-access, but you have to the desktop edition of sql server (or sql
express).

The "Jet" database engine is not a server, nor even a seperate process that
runs on your computer. You code is reading data directly from the mdb file
and there is not an ability to make requests to JET.

 

[Jamie Collins]

jet is not a server, nor is it even another process thread.

So, JET is synchronous. The processing, and code that is running is strictly
your single thread of code. This is not difference then opening a text file
and reading it line by line.

If you really do need asynchronous operations, then you can continue to use
ms-access, but you have to the desktop edition of sql server (or sql
express).

The "Jet" database engine is not a server, nor even a seperate process that
runs on your computer. You code is reading data directly from the mdb file
and there is not an ability to make requests to JET.

Thanks for your reply. I think we may have differing views on what
constitutes 'asynchronous' <g>.

To clarify, when using ADO synchronously, or DAO at all, my
application will pause execution at the line

myRecordset.Open

and execution will not continue past this line until the query has
both executed and fetched the resultset into the recordset.

Jet may not be a server (I'm happy to defer to your superior knowledge
here) but ADO is a server i.e. a COM server. Hence, with a Jet
database source (including Access 2007 engine) I can asynchronously
connect, asynchronously execute and asynchronously fetch. I stress:
this works with Jet and works well. And note this is not my definition
of 'asynchronous', rather it's the one used in the ADO documentation
from Microsoft.

So when my application's execution hits the line

myRecordset.Open , , , , adAsyncExecute + adAsyncFetch

execution pauses only momentarily before continuing while the query
executes and/or fetches. At this point there must be at least two
threads: one for my application and one for ADO.

This is extremely useful and one of the features of ADO that sets it
apart from DAO (not that this thread is about point scoring <g>). Not
only can my application show a marquee style progress bar (there are
no 'execution progress' events) the user can use other functionality
of the application while waiting for the ADO process to complete. The
only things the user cannot do is use ADO functionality (I disable it
via the front end) and quit my application because, and this is the
reason for my post, I cannot cancel, close or set to Nothing the ADO
objects. Sure, there are now two (or more) threads hitting the CPU so
the query may execute even slower (and of course ADO + Jet is slower
than DAO + Jet to begin with) but at least the user can get on with
other stuff in the meantime; with synchronous execution, they may
think my app has hanged.

My *guess* is that ADO is coded to always teardown cleanly e.g. if the
object is executing or fetching the Cancel method is implicitly called
and waits for the provider to return an 'all clear' message before the
ADO objects can be released, on the theory that otherwise a memory
leak could occur (as happens with DAO -- or have they fixed this? And
if they have fixed it, why does everyone code as if they haven't i.e.
explicitly tearing down objects and in a set order?!)

So, I do not agree your assertion that "code that is running is
strictly your single thread of code" because I believe at this point
in execution that ADO is running in its own thread. But perhaps you
are on to something e.g. could it be that ADO and Jet are sharing a
thread and ADO can't quit until Jet is done?

PS As I mentioned, I already use SQL Server asynchronously and it
indeed returns quickly from a (synchronous) call to Cancel. However, I
sometimes use Jet and I'd like to understand what the limits to its
asynchronous operations are via ADO. In case you are not convinced
such asynchronous operations as possible at all with Jet, see:

Microsoft Access 2000 Technical Articles
Microsoft Access 2000: Class, Form, and Report Modules
http://msdn2.microsoft.com/en-us/library/aa139986(office.10).aspx

"After referencing a library, such as the Microsoft ActiveX Data
Objects 2.1 Library, you can build custom events around the ADO events
for the Connection and Recordset objects. These events can enable
asynchronous data access that lets your application respond to users
even while it remains ready to respond to a completed connection or
the availability of a fetched set of records."

Thanks again,
Jamie.

--

 

[Albert D. Kallal]

So when my application's execution hits the line

myRecordset.Open , , , , adAsyncExecute + adAsyncFetch

execution pauses only momentarily before continuing while the query
executes and/or fetches. At this point there must be at least two
threads: one for my application and one for ADO.

I don't believe the above works with JET. While the ado my be async, the
instance that ado tries to read data from JET..it going to wait..and it
going wait until all data is read before the request is released. (or at
least until the buffer is full)

This is extremely useful and one of the features of ADO that sets it
apart from DAO

the problem is that when you use ADO->JET, you using the same code base as
DAO...

DAO can operate async with sql sever..but not with JET...

and of course ADO + Jet is slower
than DAO + Jet to begin with) but at least the user can get on with
other stuff in the meantime; with synchronous execution, they may
think my app has hanged.

Like I said, I was not aware the above was possible. Have you experienced
success here, or are you hopping to obtain async operations here?

PS As I mentioned, I already use SQL Server asynchronously and it
indeed returns quickly from a (synchronous) call to Cancel.

Yes, and you can accomplish with DAO to sql server also.

If you using JET, then your code has to wait, be it ADO, or DAO.

While a thread may, or may not be spawned (I really don't know), JET is on
the same process as your code..and it waits...no diffent then reading a text
file line by line. While there many be inter-process com objects (your code
to ADO), THAT IS 100% different then ADO operating async from your code. ADO
is not operating async, it is the request to the server that operates async.

These events can enable asynchronous data access that lets your application
respond to users even while it remains ready to respond to a completed
connection or the availability of a fetched set of records.

Yes but that only going to apply if the data source can async grab data..and
JET can not (as far as I know). JET is not a separate process here. (sql
server is...and in fact is a separate machine!).

When you code executes a ADO command, you code MUST wait until ADO is done.
If ADO is making a request to sql server, then your code STILL HAS to wait
until that request is done. ADO is not async, it can *make* async
requests..and that is grand canyon of difference. ADO does not continue
while your command is sent to sql server (ADO command is 100% completed, and
*then* ado continues. That is even the case for an async request). Only once
that command is completed does ADO continue.

The fact that you don't launch, start, stop, and kill ADO as a process kind
of make the point that ADO is not a process, it library of code your
calling, and just like calling a standard subroutine in your code, your call
code waits. If you have sql server, then you can send the request..and go on
your merry way. However, that "system" your making the request to has to be
able to operate independent of your code. ADO, or DAO/jet is simply a
library of code you call, and your code waits until that code is done.

As to how, or what happens when data starts to stream form sql server on a
async request, I don't know, but I suspect some type interrupt or something
else must occur. But, you code calls to ADO are still sync.

Regardless, since JET is not a server object, and does not run as a separate
task, then it going to function SYNC.

Just because JET, or the File scripting object (FSO) are com objects don't
mean they can run async. When you use the FSO to read a text file...you wait
for each line of data. Use of a com object like FSO, or ADO does not hint,
or imply async operations.

Now, ADO supports assync operations, but that going to have to be used with
a data source that able to operate async.

I open to be corrected on the above....

 

[Robert Morley]

Like you, I've had some experience with using ADO against Jet sources
asynchronously, and it seems to work quite well. I've never tried
cancelling an operation myself, but my guess at what's going on would be
this:

As Albert has said, Jet is really not designed to run asynchronously per se.
ADO can "fake it" to some degree, but when it comes to cancelling, I suspect
that Jet MUST finish the request no matter what. ADO is aware of this, and
as you had thought, it probably waits for an "all clear" from Jet before
moving on. Since Jet feels the need to complete its process, you're stuck
waiting for that to finish, even though ADO is simply discarding any further
data it receives.

I'm only theorizing here, myself, but that's what seems most likely to me.

Oh, and for Albert: properly designed, out-of-process COM servers will be
asynchronous by nature, as they're started on a completely separate thread
from your main process. That only applies to ActiveX EXE's, however, not
DLL's and the like...at least AFAIK; asynchronous COM is not my specialty.



Rob

 

[Albert D. Kallal]

Oh, and for Albert: properly designed, out-of-process COM servers will be

asynchronous by nature, as they're started on a completely separate thread
from your main process. That only applies to ActiveX EXE's, however, not
DLL's and the like...at least AFAIK; asynchronous COM is not my specialty.

*very* interesting. and, I kind of guessed that is the case. The problem is
that while a com object could easily be asynchronies, the "com" messaging
system is a just a covenant way to call some library code, and that code is
*usually* sync code, not ascync code.

So, yes, I guess this means that a com object by its nature is async,
messaging to the com object is async, but in practice..most of the time it
not used that way, and you just calling some libary code.....

 

[Robert Morley]

So, yes, I guess this means that a com object by its nature is async,

messaging to the com object is async, but in practice..most of the time it
not used that way, and you just calling some libary code.....

Exactly. Then again, most of the time, you're also using ActiveX DLLs,
which are in-process, and synchronous. ActiveX EXE's (Word and Excel are
examples, IIRC) aren't actually all that common.



Rob

 

[Jamie Collins]

Exactly. Then again, most of the time, you're also using ActiveX DLLs,
which are in-process, and synchronous. ActiveX EXE's (Word and Excel are
examples, IIRC) aren't actually all that common.

FWIW ActiveX DLLs can be async. The usual trick is to use a Windows
timer to kick off a process, which runs in a different thread to the
calling application. After all, ADO isn't an ActiveX EXE ;-)

Jamie.

--

 

[Robert Morley]

True enough. I was referring to the default model, and excluding launching
your own threads by whatever method.


Rob

 

[DAVID]

By default, Jet uses 3 threads. The number of threads
is controlled by the thread setting in the Jet section
of your registry.

To change the number of threads, you need to stop and
restart Jet.

The multi-threaded nature of Jet causes a problem with IIS. The IIS
thread pooling mechanism assumes that all data providers are singled
threaded.

Jet, which is multi-threaded, can crash the thread-creation code IIS.
Windows thread creation is not re-entrent: you cannot create a thread
inside a thread creation call. If Jet creates a thread inside the IIS
thread creation code, the the thread will not be created correctly.

The jet dbengine options are documented in the Access help files.

(david)

 

[DAVID]

Jet is designed to run asynchronously.

However, it is not designed to run disconnected.

I expect that you will always have to wait for
startup and shutdown code if you use the Jet
provider.

(david)

 

[Jamie Collins]

On Jul 27, 5:37 pm, "Albert D. Kallal" <[email protected]>
wrote:

I've read your reply a couple of times now and, while I appreciate you
taking the time to reply, you seem to be saying, "I don't believe you
when you say you are using Jet asynchronously." I would find it
difficult to believe that it's just Robert Morely and me as the Lone
Rangers on this one and I am perplexed as to why the Access MVP who
knows has IMO the greatest knowledge of the Jet engine has never done
this (async), moreover ever heard of it being done. However, I can't
think of why you would post if it were any other way and I'm having
trouble figuring out why you don't offer me the same benefit of the
doubt.

Have you experienced
success here, or are you hopping to obtain async operations here?

What can I say? "I very, very strongly stress in the most unequivocal
terms that this works well with Jet"? In the time it took you to
compose your reply, you could have tested it out for yourself!

Now, ADO supports assync operations, but that going to have to be used with
a data source that able to operate async.

I open to be corrected on the above....

If you are indeed open to be corrected, I'm at a loss what I could do
to convince you. I posted some 'boilerplate' code; would a fully
working example be required? I posted a link to an Access article
mentioning asynchronous operations; would a more unequivocal article
be required?

Jamie.

--