troubleshooting Question

Retrieving Batches of Rows in ADO Recordset - Microsoft Access

Avatar of TechMommy
TechMommyFlag for United States of America asked on
Microsoft AccessMicrosoft SQL Server
15 Comments1 Solution794 ViewsLast Modified:
I have a large recordset which I want to use as the foundation for an Access form. The problem is that I do not want to bring back all of the rows in the recordset to the form. I am able to use the ADO PageSize property to page through a recordset once it is on the client, but I still experience a performance hit populating the recordset with the 5000 plus rows that I am retrieving from the server. I need a way to page through the recordset on the server, retrieving only 100 rows at a time.

Here is the code I have so far:

       Set rst = New ADODB.Recordset
        rst.ActiveConnection = CurrentProject.AccessConnection
        rst.LockType = adLockOptimistic
        rst.CursorType = adOpenKeyset
        rst.CursorLocation = adUseServer
        
        Set rst2 = New ADODB.Recordset
        rst2.ActiveConnection = CurrentProject.AccessConnection
        rst2.LockType = adLockOptimistic
        rst2.CursorType = adOpenKeyset
        
        rst.Open "SELECT * From vwProjectBuyerReport"         
        rst.PageSize = 25
        
        rst2.Open "SELECT *  FROM tblTempRecordset WHERE ProjectID = 0"
        
        Dim intCount As Integer
        Do While Not rst.EOF And intCount < rst.PageSize
            rst2.AddNew
            PopulateRST2
            rst2.Update
            intCount = intCount + 1
            rst.MoveNext
        Loop

        Set Me.fsubProjectBuyerReport.Form.Recordset = rst2

The line of code rst.Open takes two minutes to execute when returning 5000 rows. My goal is to NOT return the 5000 rows, but rather to page through the 5000 rows on the server, 100 rows at a time as the user clicks NEXT and PREVIOUS PAGE buttons.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 15 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 15 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros