Fetching a recordset asynchonously

[Nick Stansbury]

Hi,

I'm using the following code to fetch a results set from a sql server
database based on the other side of the world to the client - so there's
some delay in fetching large recordset (1000 records+). The most resource
effecient method appears to be to use a client-side cursor and fetch a
static disconnected recordset. When the command executs I show a little
pop-up status window - and it's this window thats causing me all of the
trouble.
Here's the code:

Public Function OpenDisconnectedRecordset(Command As ADODB.Command) As
ADODB.Recordset
DisplayStatusMessageOnPopUpForm "Preparing results set"
Dim rs As New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.CacheSize = 10
DisplayStatusMessageOnPopUpForm "Opening results set"
rs.Open Command, , adOpenStatic, adLockReadOnly, adAsyncFetch
Dim i As Integer
i = 0
While rs.State > adStateOpen
DisplayStatusMessageOnPopUpForm "Fetching additional results - " +
CStr(i) + " fetched so far"
StepProgressBarOnPopUpForm
i = i + 1
If CheckQueryCancelled() = True Then
rs.ActiveCommand.Cancel
End If
Wend
DisplayStatusMessageOnPopUpForm "Results set full - closing connection"
rs.ActiveConnection = Nothing
Set OpenDisconnectedRecordset = rs
End Function

Now what I'd like to do is show on a little "popup" window the total number
of records fetched so far, the total number of records to be fetched, the
estimated time left, and a progress bar representing exactly that. I'd also
like the pop-up window to have the capacity to "cancel" the running query.

There are three problems -

1) How do you find out how many records have been fetched by an async
fetched recordsed at any given point in time? I've tried getting the record
count - but that fetches every record down counts them - rather than telling
you how many have been returned so far. Is there anyway to achieve this? The
way I'm doing it above is a total fake - it's just counting the number of
iterations through a random loop - the number is totally meaningless.
2) There's no way I can see of returning the total recordcount *before* the
results set - output parameters (which are the obvious choice) are only
returned at the end of the stream from the database - so you can't get to
their values until you've returned any and all results sets in full. Anyway
around this? Any ideas how I could achieve this? I'd like to avoid a second
database call if possible.
3) A cancel button on the popup form just doesn't seem to work. What I was
hoping was that I could put a cancel button on my little pop-up, use it's
event handler to set a boolean flag to cancelled, and then use the
"CheckQueryCancelled" call above to catch the condition and cancel the
query. I know that I'm trying to fake some sort of threaded behaviour here -
and it just isn't working. The command button just plain doesn't work (it
doesn't "depress" when clicked, no events fire etc.) whilst the other code
is running. Any ideas here?

Any and all help would be very gratefully received!

Nick

 

[peregenem]

How do you find out how many records have been fetched by an async
fetched recordsed at any given point in time?

Have you tried using the recordset's _FetchProgress event?

Change

Dim rs As New ADODB.Recordset

to

Private WithEvents rs As New ADODB.Recordset

and relocate it to the General Declarations section of your class
module (you *are* using a class module, right?) Change the lefthand
'(General)' dropdown to 'rs' then the righthand dropdown to
'FetchProgress'. You'll see a couple of useful parameters (Progress and
MaxProgress).

 

[Robert Morley]

Okay, let's take these in order.

I won't say I'm *the* most learned on ADO/SQL Server, so there may be
options I'm unaware of, but I can at least get you started, and maybe one of
the MVP's will know something I don't.

Let me start at the end. If you *just* want to do #3 without a lot of extra
effort, add the line "DoEvents" somewhere inside your loop, and you'll find
you can now depress the button as expected. If you're going to implement
the rest of this, though, you don't need the DoEvents, as you will truly be
multi-threading instead of faking it.

I'll walk you through the steps to do this, as a learning exercise, but I'll
present the final code at the end and you can just cut & paste that if you'd
rather. This method will simultaneously solve all your problems, though as
I remember it, the estimated total number of records is completely
inaccurate, so probably won't do you a whole lot of good. If there's a
better way of figuring that out (short of querying the recordset twice), I
don't know it. I'm also typing this mostly from memory, so if you run into
any problems, let me know. So here goes:

- First, you'll need to move this into a class module if it's not already.
If you're implementing this inside a form, which it sounds like you are,
well, they're class modules as well, so you're already there.
- Move the Dim rs as New ADODB.Recordset to the form level (i.e., at the
top, underneath any Option Compare/Option Explicit statements), and change
it to "Private WithEvents rs As ADODB.Recordset". Note that you can't use
"New" with "WithEvents", and you can only use "WithEvents" as the Class/Form
level.
- Now you'll see "rs" as an object in the drop-down list at the very top of
your code...damned if I know the proper name for it...it's where you'd
normally see the form or control names. So select rs, then in the combo box
to the right of that, select "FetchProgress". As you can see in the
procedure header, it passes you "Progress" and "MaxProgress" variables.
BOTH of these can be updated at any time during the reading of the
recordset, so you can't just initialize your progress bar with a maximum at
the beginning, you need to be constantly re-setting the maximum and the
current progress. You'll need to change this in your StepProgressBar
routine. Anyway, this is where the bulk of your existing loop code will now
go. This code will execute once per record, and the form will actually be
idle in-between, so your button should work now without the need for a
"DoEvents". So here's your code fragment for this...

DisplayStatusMessageOnPopUpForm "Fetching additional results - " +
CStr(Progress) + " fetched so far"
StepProgressBarOnPopUpForm 'You'll have to change this to incorporate
Progress and MaxProgress
If CheckQueryCancelled() = True Then 'Note, you can actually reduce this
to just
'If CheckQueryCancelled() Then
rs.ActiveCommand.Cancel
Else
rs.MoveNext
End If

- Now you need to initialize the recordset, so going back to your original
function, this is how it needs to look now. Note the addition of the "Set
rs = New..." line, and the fact that the ONLY purpose of the sub is to set
up the recordset and open it. I've also changed it to no longer be a
function since rs is now at the form level anyway. It probably doesn't need
to be public, either, but I'll leave that for you to decide.

Public Sub OpenDisconnectedRecordset(Command As ADODB.Command)
DisplayStatusMessageOnPopUpForm "Preparing results set"
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.CacheSize = 10 'When using a client-side cursor, this is fairly
irrelevant, I think
DisplayStatusMessageOnPopUpForm "Opening results set"
rs.Open Command, , adOpenStatic, adLockReadOnly, adAsyncFetch
End Sub

- Finally, you need to address what happens when all the records are
fetched, so select the "rs" object and the "FetchComplete" event, and add in

DisplayStatusMessageOnPopUpForm "Results set full - closing connection"
Set rs.ActiveConnection = Nothing 'You missed the "Set" here
rs.Close
Set rs = Nothing 'Not sure if you can do this within an rs-based Event.
If you get an error, put it in the Form_Close event instead.

Okay, I *think* that covers everything, so here's what your final code
should look like:

----------------------------------------------------
Private WithEvents rs As ADODB.Recordset

Public Sub OpenDisconnectedRecordset(Command As ADODB.Command)
DisplayStatusMessageOnPopUpForm "Preparing results set"
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.CacheSize = 10
DisplayStatusMessageOnPopUpForm "Opening results set"
rs.Open Command, , adOpenStatic, adLockReadOnly, adAsyncFetch
End Sub

Private Sub rs_FetchProgress(ByVal Progress As Long, ByVal MaxProgress As
Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As
ADODB.Recordset)
DisplayStatusMessageOnPopUpForm "Fetching additional results - " +
CStr(Progress) + " fetched so far"
StepProgressBarOnPopUpForm(Progress, MaxProgress) 'You'll have to change
this to incorporate Progress and MaxProgress
If CheckQueryCancelled() = True Then 'Note, you can actually reduce this
to just
'If CheckQueryCancelled() Then
rs.ActiveCommand.Cancel
Else
rs.MoveNext
End If
End Sub

Private Sub rs_FetchComplete(ByVal pError As ADODB.Error, adStatus As
ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
DisplayStatusMessageOnPopUpForm "Results set full - closing connection"
Set rs.ActiveConnection = Nothing 'You missed the "Set" here
rs.Close
Set rs = Nothing 'Not sure if you can do this within an rs-based Event.
If you get an error, put it in the Form_Close event instead.
End Sub
----------------------------------------------------



Good luck,
Rob

 

[Robert Morley]

As noted in my post, you can't use "New" and "WithEvents" together...unless
they've added that in Access 2003 or later.



Rob

 

[Robert Morley]

Woops...ignore the "Let's take these in order"...that changed as I typed the
message. <blush>


Rob

 

[peregenem]

As noted in my post, you can't use "New" and "WithEvents" together

Good catch

...unless
they've added that in Access 2003 or later.

No, it's still VBA6 (and no doubt always will be).

 

[Nick Stansbury]

Rob,

Thanks! A perfect answer to all of the questions. It's been a long time
since I wrote vb6 code - I'd forgotten abount DoEvents entirely

The other stuff about wrapping it into a class, and adding an event
handler is a great idea and would sort the problem as well. I hadn't
realised that getting access to the fetch status had to be done through an
event handler.

Thanks a bunch

Nick

 

[Nick Stansbury]

Thank you very much for this - it works great thanks!

Nick

 

[Nick Stansbury]

ALSO POSTED ELSEWHERE - But this post follows on from this earlier post so
I'm copying it here

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

 

[Robert Morley]

I haven't dealt with output parameters, so have nothing to suggest there,
but try decompiling your project. It never ceases to amaze me just how
easily Access databases/projects become corrupted, and cause all kinds of
seemingly random errors.



Rob

 

[Nick Stansbury]

Rob,

Thanks for the idea. I hadn't thought of it. I tried that twice this
morning to no affect. it still just rolls over and dies when I touch either
output parameters or just close the connection! Did you check and see if
this problem occurs at your end?

I got the latest v. of MDAC / ADO and it's made no impact either

Nick

 

[peregenem]

SUMMARY - Closing a connection, or getting a parameter's value in an event
handler for executecomplete in an ado connection causes an irrecoverable
crash

Tests OK for me:

' <SQL>
CREATE PROC TestProc
@test_out INTEGER OUTPUT
AS
SET @test_out = 1
' </SQL>

' --<Module1>--
Sub Test()
Dim c1 As Class1
Set c1 = New Class1
End Sub
' --</Module1>--

' --<CLASS1>--
Option Explicit

Private WithEvents m_Conn As ADODB.Connection

Private Sub Class_Initialize()
Set m_Conn = CreateObject("ADODB.Connection")
m_Conn.Open _
"Provider=SQLOLEDB.1;Data Source=MyServer;" & _
"Initial Catalog=MyDB;User Id=Himher;password=blah;"
Dim cmd As ADODB.Command
Set cmd = CreateObject("ADODB.Command")
With cmd
.ActiveConnection = m_Conn
.CommandType = adCmdStoredProc
.CommandText = "TestProc"
.Parameters.Append .CreateParameter("@test_out", _
adInteger, adParamOutput, , 0)
.Execute
End With
End Sub

Private Sub m_Conn_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 col As VBA.Collection
Set col = New VBA.Collection
With pCommand.Parameters.Item(0)
col.Add .Name, CStr(.Value)
col.Add .Value, .Name
End With
End Sub
' --</CLASS1>--

I thought it a little odd you would want to add to the collection using


col.Add .Name, .Value

because presumably you'd want to add the Value and use the Name as the
index, so you would have them round the other way. If your Value is
numeric, as is mine in the demo above, you'll get an error here (remove
the CStr which fixes). Could it be that getting these parameters to the
Add method round the wrong way causes a simple run-time error which
causes big upset to the system? Just a thought.

 

[Nick Stansbury]

Thanks for the help. I'll test this and get back to you. Could you carry on
watching the thread?

Nick

 

[Vadim Rapp]

Hello Robert:
You wrote on Thu, 29 Sep 2005 11:21:44 -0400:

RM> As noted in my post, you can't use "New" and "WithEvents"
RM> together...unless they've added that in Access 2003 or later.

actually, if you think about it, this could not be added ever, neither in
Access, nor elsewhere: it's unlikely that the application would recompile
itself "on the fly".

Vadim

 

[Robert Morley]

Personally, I prefer having the control of when something is created and
destroyed, so it's not really an issue for me, but for the sake of
discussion....

Why would the project have to recompile itself on the fly? If it's
early-bound, the events and their parameters are known. Why not simply have
the object initialized & destroyed with the form (or class module), and
listening for events from said object?



Rob

 

[Robert Morley]

Sorry, no, don't have SQL Server on this machine, so I can't readily do so.

 

[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

 

[Vadim Rapp]

Hello Robert,
You wrote in conference
microsoft.public.access,microsoft.public.access.adp.sqlserver on Mon, 3 Oct
2005 22:04:15 -0400:

RM> Personally, I prefer having the control of when something is created
RM> and destroyed, so it's not really an issue for me, but for the sake of
RM> discussion....

RM> Why would the project have to recompile itself on the fly? If it's
RM> early-bound, the events and their parameters are known. Why not simply
RM> have the object initialized & destroyed with the form (or class
RM> module), and listening for events from said object?

You are right, it was bad judgment on my part. In fact, in vb.net this
restriction already does not exist.

thanks,

Vadim Rapp