ADO 2.0 disconnect recordset 
Author Message
 ADO 2.0 disconnect recordset

Hi!,

I am using VB6.0 and ADO2.0. I've developed an application that links to a
DB2/400 database on an AS400 machine which is about 400 kilometers away. So
speed is crucial.

Currently, my app is having some speed issues. In one of the screens, I have
to populate some combo boxes. Below are the codes I wrote to do that.

    Set rst1stWt = New ADODB.Recordset
    Screen.MousePointer = vbHourglass

    'populate both source & transporter combo
    lssql = "Select cfcode from cffilep order by cfcode"

    'rst1stWt.CursorLocation = adUseClient
    'rst1stWt.Open lssql, gconCTS, adOpenStatic, adLockReadOnly

    Set rst1stWt = gconCTS.Execute(lssql)

    PopulateListBox Me.cmbSource, rst1stWt
    PopulateListBox Me.cmbTransporter, rst1stWt

    rst1stWt.Close
    Screen.MousePointer = vbDefault

'function to populate combo and list boxes
Function PopulateListBox(hWndCbo As Control, rstRstSet As ADODB.Recordset)

    'common function to populate a combo/list box
    hWndCbo.Clear

    'use disconnected recordset. it is faster
    'Set rstRstSet.ActiveConnection = Nothing

    'go to top of the recordset
    rstRstSet.MoveFirst

    'add to the combo box
    Do Until rstRstSet.EOF
        hWndCbo.AddItem rstRstSet(0)
        rstRstSet.MoveNext
    Loop

End Function

However, the whole process took about 12 seconds!

Set rst1stWt = gconCTS.Execute(lssql)  took about 1.2 secs but to populate
the two combo boxes took about 11 seconds!

So I tried with disconnected recordset in the PopulateListBox function.
However,  I've got the error below.

So I tried with :-

    rst1stWt.CursorLocation = adUseClient
    rst1stWt.Open lssql, gconCTS

to allow disconnected recordset. Now to populate the two combo boxes, it
took about 1 second only! Great! However,

    rst1stWt.Open lssql, gconCTS

itself took about 9 seconds! I am back to square one!

Is there a way to use

    Set rst1stWt = gconCTS.Execute(lssql)

and the disconnected recordset so that the whole process is just about 2
seconds?

Pls help. Any help will be greatly appreciated.

Thanks.

Dennis



Tue, 25 Dec 2001 03:00:00 GMT
 ADO 2.0 disconnect recordset

Sorry, forgot to include the error message. Here it is.

Runtime error '3705':

The operation requested by the application is not allowed if the object is
open.

Any help will be greatly appreciated.

Dennis



Tue, 25 Dec 2001 03:00:00 GMT
 ADO 2.0 disconnect recordset
Hi,

Interesting problem.

From your message, I suspect that no matter which strategy you'll use the
+/- 11 seconds time lag will be there.  It seems that the data needs that
much time to travel from one end to another.  You can perform a test to
download a file from AS/400 to your local computer and compare the results.

How about an alternative?
Download all static data to your local database and get the active data from
the AS/400.

PS: It has been a while since I type the word "AS/400" on my keyboard :-).

Quote:

>Sorry, forgot to include the error message. Here it is.

>Runtime error '3705':

>The operation requested by the application is not allowed if the object is
>open.

>Any help will be greatly appreciated.

>Dennis



Tue, 25 Dec 2001 03:00:00 GMT
 ADO 2.0 disconnect recordset
Julius,

One of my project requirement is that all databases must remain in the
AS/400 ie. no duplicate database ,efforts,support etc.

Looks like local database is out of the question. Anymore ideas?

Thanks.



Tue, 25 Dec 2001 03:00:00 GMT
 ADO 2.0 disconnect recordset
FWIW I had a similar problem. I found that using the data bound combo
instead of stuffing my own list with AddItem was much faster.

My list was about 4-5 thousand names and putting them in with AddItem was
taking forever. It takes less than a second with the data bound combo.

Sharon

Quote:

> Julius,

> One of my project requirement is that all databases must remain in the
> AS/400 ie. no duplicate database ,efforts,support etc.

> Looks like local database is out of the question. Anymore ideas?

> Thanks.



Tue, 25 Dec 2001 03:00:00 GMT
 ADO 2.0 disconnect recordset
But how user-friendly is a combo box with 5000 names in it?  How long
does it take for someone to find a particular value (even though the
combo box is loaded quickly)?

For something with this number of elements, you may want to seriously
consider another control to hold the data.  Use a tree-view that
partitions the 5000 elements somehow, so you can load part of the data
at a time.  This would also help with the amount of time it takes to
load the data, since you can load it small parts at a time.

Kirk Allen Evans



Quote:
> FWIW I had a similar problem. I found that using the data bound combo
> instead of stuffing my own list with AddItem was much faster.

> My list was about 4-5 thousand names and putting them in with AddItem
was
> taking forever. It takes less than a second with the data bound combo.

> Sharon


> > Julius,

> > One of my project requirement is that all databases must remain in
the
> > AS/400 ie. no duplicate database ,efforts,support etc.

> > Looks like local database is out of the question. Anymore ideas?

> > Thanks.

--
Kirk Allen Evans

(Do not send mail to the Deja News account used

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.



Tue, 25 Dec 2001 03:00:00 GMT
 ADO 2.0 disconnect recordset
Have you tried downloading a file with the same size as your data and clock
it? (just curious)

Or You can try downloading the necessary referential data during program
connection set-up on your local machine and You can upload it to your combo
box during form open.  (I just hope your  referential data do not change
frequently).

Quote:

>Julius,

>One of my project requirement is that all databases must remain in the
>AS/400 ie. no duplicate database ,efforts,support etc.

>Looks like local database is out of the question. Anymore ideas?

>Thanks.



Fri, 28 Dec 2001 03:00:00 GMT
 ADO 2.0 disconnect recordset
You're right. 5000 names is VERY user unfriendly. This was initially how the
customer wanted it.

I'm consulting here and trying to delicately suggest more user friendly
methods to a customer that is moving from a legacy and paper system. The
real answer is that all these names did not belong in one list. I think I
have convinced them to let me split the list up by categories. (Still
waiting final approval, but at this point it's already coded that way :-)
).

Thanks for your ideas.

Sharon

Quote:

> But how user-friendly is a combo box with 5000 names in it?  How long
> does it take for someone to find a particular value (even though the
> combo box is loaded quickly)?

> For something with this number of elements, you may want to seriously
> consider another control to hold the data.  Use a tree-view that
> partitions the 5000 elements somehow, so you can load part of the data
> at a time.  This would also help with the amount of time it takes to
> load the data, since you can load it small parts at a time.

> Kirk Allen Evans



> > FWIW I had a similar problem. I found that using the data bound combo
> > instead of stuffing my own list with AddItem was much faster.

> > My list was about 4-5 thousand names and putting them in with AddItem
> was
> > taking forever. It takes less than a second with the data bound combo.

> > Sharon


> > > Julius,

> > > One of my project requirement is that all databases must remain in
> the
> > > AS/400 ie. no duplicate database ,efforts,support etc.

> > > Looks like local database is out of the question. Anymore ideas?

> > > Thanks.

> --
> Kirk Allen Evans

> (Do not send mail to the Deja News account used

> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.



Sat, 29 Dec 2001 03:00:00 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. ADO fails to release Recordset cursor after opening disconnected Recordset

2. ADO fails to release Recordset cursor after opening disconnected Recordset

3. Best way to relate disconnected ADO recordsets

4. ado disconnected recordset

5. ADO Data Control with Disconnected Recordsets

6. reporting with ADO disconnected recordsets

7. Need Help Reporting on Multiple ADO (Disconnected) Recordsets

8. Query out of a disconnected ADO recordset

9. Disconnected ADO Recordset-Updating

10. Can I update a disconnected recordset in ADO

11. Copying Disconnected ADO Recordset

12. ADO Disconnected Recordset Index


 
Powered by phpBB® Forum Software