We help IT Professionals succeed at work.

How to pump huge records into ADO RecordSet ?

b_k_cheah
b_k_cheah asked
on
89 Views
Last Modified: 2012-06-08
I am using ADO to retrieve the data and put into recordset. But the records is huge : 50,000++. Everytime I execute query, it takes some time to populate all records into recordset, before user can view it. Is there any way to break it into small pieces and user at least can view some records in recordset ? instead of waiting for all records downloaded into ADO recordset. I knew Crystal Report do it very well. Any idea ?
Comment
Watch Question

Commented:
1. try to minimze the field that is placed in the recordset. Call only the field that is only used.

2. maybe specify the condition in your query will minimeze the calling of unecessary records.

Regards

Commented:
hi d k cheah,
  first of all you write the select statement having 50000++ records in a stored procedure so that access is faster as it is precompiled.
(1) You can page the records say 50 or 100 in page using the page and absolute page property of recordset.
(2) you can populate and display first 100 records in mshflexgrid and have a next button at the bottom. on clicking that button, you can display the next 100 by using rs.move(100) method to directly navigate to the 101st record in the same grid after clearing and repopulating.

I think this should help you.

regards
Senior Tech Consultant
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
USe asynchronous execute for ur recordset.
Valliappan ANSenior Tech Consultant

Commented:
pritam_dewan, you have repeated, what I have mentioned already.

Author

Commented:
Thanks for you all advise. But, It is better if you all can send me the sample of code. I prefer it. I am interesting on valli_an and javvb solutions. Appreciate if you can post the sample ASAP.

Thanks in advance.

Author

Commented:
Actually I do it in ASP. Do you think adAsyncFetch can work in ASP? Please advise if can.

Thank you.
just use paging, im sure you have seen it done before in other ASP sites. Example

1st page
Records 1-10 of 110
1
2
3
.
.
10

Next Button

2nd page
Records 11-21 of 110
11
12
13
.
.
21

Previous Button     Next Button


Good Example of Paging
http://www.stardeveloper.com/asp_paging_1.asp
Valliappan ANSenior Tech Consultant

Commented:
Have a look at this:

http://www.adodb.com/errors_new_result.asp?errors_id=743
(Use the ADO FetchProgress and FetchComplete Events )

http://www.adodb.com/errors_new_result.asp?errors_id=364
(Open method)

http://support.microsoft.com/support/kb/articles/q224/3/32.asp
(PRB: ADO Recordset Open Method May Behave Synchronously Even if adAsyncFetch is Specified)

and

http://www.aspfree.com/articles/kbmdac.asp
(Hope this link be of any help to you)

Cheers.




Valliappan ANSenior Tech Consultant

Commented:
Hi b_k_cheah

Thanks.

Since you have graded 'C', anything more you expected in the answer? If so, you can ask for it, before grading.

Have a look at:
https://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.