Reproducable ADO crash - in executecomplete event

[Nick Stansbury]

SUMMARY - Closing a connection, or getting a parameter's value in an event
handler for executecomplete in an ado connection causes an irrecoverable
crash and corruption of ms access & the vba modules / code.

Hi,

Apologies for posting to three groups - not sure where this one fits. I
have discovered (and suffered all day with) a really really irratating ado
bug - it causes access / vba to crash completely, and corrupts the access db
/ vba modules so that they become unusable. I've got it pretty well nailed
down and wonder whether anyone can help!

I have been dealing with a series of long running queries against a sql
server on the other side of the world that I wanted to execute
asynchronously (but consequtively). No problems in getting them to execute -
but I also wanted to abstract the connection management (i.e. it started by
opening the connection, fired off the async commands - and then an event
handler for executecomplete read the output parameters into a collection and
closed the conection).

Either reading the value of a parameter in this event handler or closing
the connection leads to a crash - not when the line executes but in the
"non-vb code" section immediately afterwards. Even if I put a breakpoint on
the line of code in question and then try and "stop" execution rather than
let it carry on the crash happens anyway. Doing either of these things
causes the crash.

Please can anyone help! I'm pretty desparate as I don't want to have to drop
this feature - it'd be such a pain to do it synchronously.!

Thanks in advance

Nick


So here's some simplified code:

Private Sub MyConnection_ExecuteComplete( _
ByVal RecordsAffected As Long, _
ByVal pError As ADODB.Error, _
adStatus As ADODB.EventStatusEnum, _
ByVal pCommand As ADODB.Command, _
ByVal pRecordset As ADODB.Recordset, _
ByVal pConnection As ADODB.Connection)

dim cmd as adodb.command, param as adodb.parameter
for each cmd in me.ExecutingCommands
if cmd is pCommand then
'parse the output parameters into a collection i.e.
for each Param in pCommand.Parameters
OutputParameters.add Param.Name, Param.value '<-- This
line will cause the crash
Next
if IndexOf(cmd, ExecutingCommands) = ExecutingCommands.Count
then
'this is the last command in the seriea
pConnection.close '<-- As will this line here
raiseevent ExecutionComplete
end if
end if
Next
End Sub

 

[Vadim Rapp]

Hello Nick,
You wrote in conference
microsoft.public.access,microsoft.public.access.adp.sqlserver,microsoft.public.data.ado
on Fri, 30 Sep 2005 19:19:57 +0100:

NS> SUMMARY - Closing a connection, or getting a parameter's value in an
NS> event handler for executecomplete in an ado connection causes an
NS> irrecoverable crash and corruption of ms access & the vba modules /
NS> code.

where is this connection coming from? is it separate connection that you
create as new adodb.connection, or it is somehow related to Access own
connection? Please post the code where you declare the connection and open
it.

Vadim Rapp

 

[Nick Stansbury]

The connection is to a remote SQL server - here's the code:

Private Sub OpenConnection()
Dim MyCn As New ADODB.Connection
MyCn.CursorLocation = adUseClient
MyCn.Open ("Driver={SQL
Server};Server=xxx.xxxxxx.xxx;Database=xxx;Uid=xxx;Pwd=xxx;")
Set MyConnection = MyCn
End Sub

Thanks for your help

NIck

 

[Vadim Rapp]

You posted how you opened myconnection, but not how you declared it.

For instance, if it is declared as

dim withevents myconnection as connection

instead of ...as adodb.connection

then this crash is expected.

I tried your code, but it did not crash. So, I would troubleshoot it by
isolating into a separate project with nothing else, and no other
references. Actually, I'd start troubleshooting by trying it in VB6.

Vadim





Hello Nick:
You wrote on Mon, 3 Oct 2005 09:24:16 +0100:

NS> The connection is to a remote SQL server - here's the code:

NS> Private Sub OpenConnection()
NS> Dim MyCn As New ADODB.Connection
NS> MyCn.CursorLocation = adUseClient
NS> MyCn.Open ("Driver={SQL
NS> Server};Server=xxx.xxxxxx.xxx;Database=xxx;Uid=xxx;Pwd=xxx;")
NS> Set MyConnection = MyCn
NS> End Sub

NS> Thanks for your help

NS> NIck

NS> ??>> Hello Nick,
??>> You wrote in conference
??>>
NS> microsoft.public.access,microsoft.public.access.adp.sqlserver,microsoft
NS> .publ ic.data.ado
??>> on Fri, 30 Sep 2005 19:19:57 +0100:
??>>
NS>>> SUMMARY - Closing a connection, or getting a parameter's value in an
NS>>> event handler for executecomplete in an ado connection causes an
NS>>> irrecoverable crash and corruption of ms access & the vba modules /
NS>>> code.
??>>
??>> where is this connection coming from? is it separate connection that
??>> you create as new adodb.connection, or it is somehow related to Access
??>> own connection? Please post the code where you declare the connection
??>> and open it.
??>>
??>> Vadim Rapp
??>>

With best regards, Vadim Rapp. E-mail: (e-mail address removed)

 

[Nick Stansbury]

It's declared as an adodb.connection

I'll break this out into vb6 and see what happens.

Thanks

Nick

 

[Nick Stansbury]

Solved!

I solved this one (I think!) - the key is that the recordset's status is
different from the command's status when the event fires. So - when the
command's status property is adStateOpen, the recordset's property is
adStateFetching - if you put a Do until pRecordset.state < 8: doEvents :
Loop in there it works ok. But that kind of defeats the point. I think this
is what's causing the crash - that I close the connection whilst the
recordset is still fetching records.

I'll put that one down to experience...

Thanks for all your help

Nick

 

[Robert Morley]

You may want to listen to the Recordset's FetchProgress or FetchComplete
events instead of the Command's ExecuteComplete event.



Rob

 

[Nick Stansbury]

These events don't seem to fire with suffecient reliability to be able to
use. I've tried playing with background fetch size and initial fetch size -
they don't seem to be reliable. Frankly I think I'm going to have to build
an activeX dll to sort this problem out - there at least I'd have more
control over what goes on.

Nick

 

[Gary Walter]

Hi Nick,

I am not an ADO expert (I mostly use DAO)
so I apologise if the following is irrelevant, but ...

When I read Mary Chipman's book, she mentioned
that you can only trap events in a *class* module and
you must use WithEvents keyword when you declare
your variable in the Declarations section (pgs 262-263).

 

[Robert Morley]

I've never had them NOT fire, so I don't know what to tell you.


Rob

 

[Nick Stansbury]

Robert,

Thanks. As far as I can tell to make them fire you need to set Initial
Fetch Size etc. to be very low numbers so they fire with a reasnoable degree
of regularity. The fetch event is ok, it fires pretty regularly - but the
fetch_complete event doesn't seem to fire every time. Which is annoying.
Anyway - I've circumnavigated this problem I think (I'm just faking
multi-threading with doEvents and Sleep()'s and getting around it that way.)

Thanks for all your help Rob.

Nick