Display records number X to Y (1 Viewer)

[G37Sam]

What query or SQL statement would I have to use as a multiple form's recordsource to have it display record numbers 100 to 200 for example?

A solution I thought off
Adding an extra field to the table, and doing this before opening the form

Dim tb as DAO.recordset, counter as int
set tb = currentdb.openrecordset("myTable")
counter = 0
do while tb.eof = false
tb.edit
counter = counter + 1
tb!myRecordNumber = counter
tb.update
tb.movenext
loop
tb.close

then setting the forms recordsource to

select * from myTable where myRecordNumber between 100 and 200

Is there a quicker way to do this? My backend is sitting on a server online, and updating all 8712639 records everytime the form is opened might not be the smartest or quickest approach

 

[WayneRyan]

G,

Looking at the way you assigned the RecordNumbers, I draw the conclusion
that the ordering is random.

Create a view on the server. Use the "Select Top 100 ... Order By RND()"
syntax and sort by a new field based on the RND() function.

If your table has RETAINED the assigned numbers then you don't need the
RND() function and just use the Top predicate in successive views.

The first view isolates the first "n" records. The second view gets the
Top 100 that are not in the first query.

Wayne