Query on Record Number

Posted on 2008-11-09
Last Modified: 2012-05-05
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?
Question by:Powerhousecomputing
    1 Comment
    LVL 3

    Accepted 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

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now