Query on Record Number

I want to pull out a selection data but only within a range of record numbers.  e.g. record numbers 1-100  How do I do this without having a number field.  Can I do it just by record number?  Normally I would have my query which has between.... and.... in the criteria, but how do I refer to the record number rather than a field name?
Who is Participating?
Heathcliff74Connect With a Mentor Commented:
MS Access does not allow selection on recordnumbers. It only allows you to select on an auto-increase-field, but I guess that is not what you mean. However, through ADO-recordsets it is possible to use paging. Here is some sample vb-code:

iPageSize = 100
strPageCurrent = 1 ' Select the first page

'Open the connection string
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connstring

set cmd = server.CreateObject("adodb.command")

set cmd.ActiveConnection = conn

'set sql statement to a local variable
strSql = "SELECT * FROM table1"

With cmd
    .CommandType = adCmdText
    .CommandText = strSQL
End With

Set rs = Server.CreateObject("ADODB.Recordset")

' Set cursor location and pagesize
rs.CursorLocation = adUseClient
rs.PageSize = iPageSize

' Open Recordset object and
rs.Open cmd

'Disconnect from database
cmd.ActiveConnection = nothing

' Get the count of the pages using the given page size
strPageCount = rs.PageCount

' If the request page falls outside the range,
' give them the closest match (1 or max)
If 1 > strPageCurrent Then strPageCurrent = 1
If strPageCurrent > strPageCount Then strPageCurrent = strPageCount

' Move to the selected page
rs.AbsolutePage = strPageCurrent
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.