[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1182
  • Last Modified:

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?
1 Solution
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Tackle projects and never again get stuck behind a technical roadblock.
Join Now