[AccessD] Paging Recordsets

Confusing me LOL

We have a database with up to 100,000 records. the design calls for
retrieval of all records at the moment. no filtering so we simply open a
recordset and grap all the records. For 100,000 this is slow. I should have
said provide access to all 100,000 without the use of filtering by the user.

What I was wondering is could we do something like the web. Bring the data
down in blocks of say 1000. User can then page through the blocks and move
within a block of records. So we would have Page 1 of  X Pages. User could
then go to Page 2 or page 3 etc. It gets a little more sophisicated after
this.

Save us getting all the records at the start.

Or any other way we could reduce the number of records without having to add
a filter that the user has to enter or select?

Martin


----- Original Message ----- 
From: "Gustav Brock" <gustav at cactus.dk>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Friday, May 28, 2004 4:38 PM
Subject: Re: [AccessD] Paging Recordsets


> Hi Charlotte
>
> I'm glad you asked - I felt lost here. What pages?
> Besides, Martin, isn't this contradictory:
>
> > What I may have is 100,000 records which have to come down from the
> > server in one hit. All of them. ..
>
> > .. What I am trying to avoid is bringing all the records down the
> > wire.
>
> /gustav
>
>
> > It it has to be updatable, you are now officially in the "unbound" camp.
> > Unfortunately, Access doesn't provide for an unbound continuous form.
> > How were you planning on presenting these "pages"?
>
> > Charlotte Foust
>
> > -----Original Message-----
> > From: Martin Reid [mailto:mwp.reid at qub.ac.uk]
> > Sent: Thursday, May 27, 2004 10:56 AM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Paging Recordsets
>
>
> > What I may have is 100,000 records which have to come down from the
> > server in one hit. All of them. What I want to do at some point is to
> > Page them down. First 100, Then Next 100 and so on.
>
> > On the Access form I want page 1 of X with navigation to any page the
> > user chooses. Of course they will need to navigate within each batch of
> > records they get.
>
> > Its much like an ASP application but using Access. I would like to
> > handle the paging by Stored Procedure on SQL Server rather than ADO but
> > will give ADO a try. What I am trying to avoid is bringing all the
> > records down the wire.
>
> > The the user can change the order by of the 100000 records and we start
> > the process all over again. So the form opens they get 100 records
> > ordered by "A", click next page they get the Next 100 ordered by "A".
>
> > They then change the order by  to "B" they get the first page again
> > ordered by "B" and so on and so on.
>
> > No filtering is allowed. THEY HAVE TO HAVE ALL 100000 records available.
>
>
> > So we would have standard navigation buttons Next Revord Previous, First
> > Last but we would also have
>
> > Page 1 of X  Then a navigation bar by page size even bringing 1000 or
> > 2000 down would be ok.
>
> > Any of this make sense??
>
> > OH and it all has to be fully updatable (<:
>
> > Martin
>
> -- 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>