Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2012
    Posts
    5

    Unanswered: Paging a Recordset in Access

    Hi dbforums,

    Can anyone possibly help me with this task that I seem to be going around and around in circles trying to accomplish.

    Is it possible to query a table to show the first say 20 rows and then show the next 20 rows and so on.

    My reason for asking is that I can create a paged recordset in ASP and query it to return a next / previous page of results, but I am currently using VBScript in an .hta wrapper. This way I cannot simply link to new pages and so I need to find a way to use the Access engine to do the work in returning the records I need in a paged way.

    If I can get a query that runs inside Access to do this then I can replicate it in VBScript code to run inside the .hta

    Any help will be gratefully appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    Ask google for database paging off top of head asp101.com has a good example
    Its the asp that will do the paging for not msaccess
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there is no inherent mechanism that I'm aware of in Access or any toher db to natively read pages of data. its usually all faked behind the scenes, or hidden away behind a class wrapper.

    but 'all' you need to do is request the data saying which page you want, the nuber of rows per page, the sort sequence and so on. and then 'just throw away the rows you don't want and return the rows you do.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2012
    Posts
    5
    Thank you myle & healdem for your feedback.

    I have done some digging based on the google search you sent.

    I will try and put into perspective what I am trying to achieve.

    Normally with an .asp set of pages I can create a recordset type query that will page my results so if I have 100 rows in the table it will serve me with say 20 or so per time with a facility to <<Previous 1|2|3|4|5 Next>> and so on. This uses a querystring url eg: http://localhost/search.asp&page=1

    In this case I am using VBScript in an .hta wrapper. I can use VBScript to query the Access database easily but I cannot find a way to page the recordset in the traditional way because it wont support querystring.

    So my dilema is finding a way to do this with VBScript as I cannot link to pages withing the .hta file.

    Sorry if its not very clear.

    Thank you

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so the variable page=1 tells the script search.asp to dispaly the fiorst n rows (upto a page)
    I'm guessing the search.asp will then pull the first n rows using say a limit clause or a top n clause depending otn eh db

    if you said you wanted page 5, and say you'd set up the number of rows per page as 11
    then search.asp will retrieve the first 65 rows that met the search criteira and sort order, binned the first 54 rows and returned rows 55..65.

    so your script to fidn the number of rows needs to know
    what page to display,
    the number of rows per page,
    the specified where clause
    the specified sort order, all of which are critivcal.

    there is no mechanism I'm aware of that can say to the db directly give me rows 55..65.

    do this modifying search.asp
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    and as this is an ASP question do you want this moving to the ASP section?
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2012
    Posts
    5
    Hi Healdem

    This is not an asp thing. There are no asp pages involved. This is using pure VBScript in an .hta with an Access database backend. The reason it is in this forum is that it is Access orientated as opposed to Oracle etc that supports ROWNUMBER.

    Cheers

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well if you cannot pass a value to the VBS function then you are stuffed with a capital F

    the one thing I can say is that this isn't an Access problem its to do with how you manipulate the data. and VBS aint VBA.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Apr 2012
    Posts
    5
    Actually I have just had a small breakthrough using a union query to add row numbers to the table in VBScript.

    If anyone else who has experience in this can help me it would be much appreciated.

    Just to re-cap

    not using asp but vbscript to create a paged recordset

    Thankyou

  10. #10
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    If you can use a two-dimensional variant array in your vbscript, here's a solution:

    Access Database: Slicer.mdb containing a Class Module (Cls_DataSlicer), a Module (Mod_Slicer) and a Table (dbo_CF_Data).

    1. Class: Cls_DataSlicer
    Code:
    Option Compare Database
    Option Explicit
    
    Private m_dbs As DAO.Database
    Private m_qdf As DAO.QueryDef
    Private m_strSQL As String
    Private m_rst As DAO.Recordset
    Private m_lngRowCount As Long
    
    Public Sub CloseRowset()
    
        If Not m_rst Is Nothing Then m_rst.Close
        Set m_rst = Nothing
        
    End Sub
    
    Public Sub OpenRowset(Optional ByVal SQL As String)
    
        If Len(SQL) > 0 Then m_strSQL = SQL
        m_qdf.SQL = m_strSQL
        Set m_rst = m_qdf.OpenRecordset(dbOpenSnapshot)
    
    End Sub
    
    Public Function GetRows(Optional ByVal Direction As String) As Variant
    
        If Direction = "Top" Then m_rst.MoveFirst
        If m_rst.EOF = False Then
            GetRows = m_rst.GetRows(m_lngRowCount)
        Else
            GetRows = Null
        End If
        
    End Function
    
    Public Property Get SQL() As String
    
        SQL = m_strSQL
        
    End Property
    
    Public Property Let SQL(ByVal Value As String)
    
        m_strSQL = Value
        
    End Property
    
    Public Property Get RowCount() As Long
    
        RowCount = m_lngRowCount
        
    End Property
    
    Public Property Let RowCount(ByVal Value As Long)
    
        m_lngRowCount = Value
        
    End Property
    
    Private Sub Class_Initialize()
    
        Set m_dbs = CurrentDb
        For Each m_qdf In m_dbs.QueryDefs
            If m_qdf.Name = "qry_cls_DataSlicer" Then
                m_dbs.QueryDefs.Delete "qry_cls_DataSlicer"
                Exit For
            End If
        Next m_qdf
        Set m_qdf = m_dbs.CreateQueryDef("qry_cls_DataSlicer")
        m_lngRowCount = 20
        
    End Sub
    
    Private Sub Class_Terminate()
    
        If Not m_rst Is Nothing Then m_rst.Close
        Set m_rst = Nothing
        If Not m_qdf Is Nothing Then m_qdf.Close
        Set m_qdf = Nothing
        m_dbs.QueryDefs.Delete "qry_cls_DataSlicer"
        If Not m_dbs Is Nothing Then m_dbs.Close
        Set m_dbs = Nothing
    
    End Sub
    2. Module: Mod_Slicer
    Code:
    Option Compare Database
    Option Explicit
    
    Private clsDataSlicer As Cls_DataSlicer
    
    Public Sub OpenSlicer(ByVal SQL As String)
    
        If Not clsDataSlicer Is Nothing Then Set clsDataSlicer = Nothing
        Set clsDataSlicer = New Cls_DataSlicer
        clsDataSlicer.OpenRowset SQL
        
    End Sub
    
    Public Sub CloseSlicer()
    
        clsDataSlicer.CloseRowset
        Set clsDataSlicer = Nothing
        
    End Sub
    
    Public Function GetRows() As Variant
    
        GetRows = clsDataSlicer.GetRows
        
    End Function
    3. Test in VBScrip: TestSlicer.vbs (remove the space between msapp. and Run)
    Code:
        Dim msapp
        Dim varArray
        Set msapp = CreateObject("Access.Application")
        msapp.OpenCurrentDatabase ("C:\Documents and Settings\Sinndho\My documents\Access\slicer.mdb")
        msapp. Run "Openslicer", "SELECT * FROM dbo_CF_Data;"
        Do While IsNull(varArray) = False
            varArray = msapp. Run("GetRows")
        Loop
        msapp. Run "CloseSlicer"
        msapp.Quit
    I guess that the code could be modified to return a Recordset (DAO or ADODB) instead of a Variant array, but I did not try.
    Have a nice day!

  11. #11
    Join Date
    Apr 2012
    Posts
    5
    Many thanks Sinndho for your post.

    I did try your example but I really needed something that would talk to the database without additional Macros.

    I have spent along time trying to find something that is quite easy to do with asp but seems to be less advertised in VBScript+Access in a client side environment.

    Fortunately I have found the answer which I have included below for future reference. Simply add this code to an .hta and hook it up to your database.

    <head>
    <script language="VBScript" type="text/vbscript">
    <!--
    dim blank, otable,otbody, orow , ocell, i, j, numcols, numrows, alldata
    blank ="&nbsp;"
    numperpage = 5
    Const adUseClient = 3
    Const adOpenStatic = 3



    Dim objConn, objRS, strQuery
    Dim strConnection, currentpage,totpages,numperpage, linenum
    Dim fieldarray(10), fieldtitle

    sub dbconnect
    Set objConn = CreateObject("ADODB.Connection")
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Your_Database_Name.mdb"
    objconn.Open StrConnection
    currentpage = 1
    StrQuery = "Select * FROM Your_Table"
    Set ObjRS = CreateObject("ADODB.Recordset")
    ObjRS.CursorType = adOpenStatic
    ObjRS.CursorLocation = adUseClient
    Set objRS.ActiveConnection = objConn
    ObjRS.Open(strQuery)
    ObjRS.Pagesize = numperpage
    totpages = objrs.pagecount
    alldata = objrs.getrows
    numcols = ubound(alldata,1)
    numrows = ubound(alldata,2)
    for i = 0 to numcols
    fieldarray(i) = objrs.fields(i).name
    next
    end sub

    sub closedb
    ObjRS.Close
    objConn.close
    Set objRS = nothing
    Set objConn = nothing
    end sub

    sub setuptable(numrows,numcols)
    'Set up Header
    With document
    .write "<table id='otable' cellpadding='10' width ='500' bgcolor = '#ffffce' border = '0'>"
    .write "<thead id = 'oTHead'>"
    end with
    set orow = document.all.oTHead.InsertRow()
    for i = 0 to numcols
    set ocell = orow.insertCell(i)
    with ocell
    .style.color = "maroon"
    .style.fontweight ="bold"
    .style.textalign = "center"
    .style.width = "125"
    .style.background = "silver"
    .innertext = fieldarray(i)
    end with
    next
    document.write "</thead>"
    'Set up body of table
    document.write "<tbody id='otbody'>"
    for i = 0 to numperpage -1
    set orow = document.all.otbody.insertrow()
    for j = 0 to numcols
    set ocell = orow.insertcell(j)
    ocell.style.textalign = "center"
    ocell.innerhtml = blank
    next
    next
    With document
    .write "</tbody>"
    .write "</table>"
    .write "<br />"

    'Add links for paging
    .write "<a href = '#' onclick = 'previouspage'>Previous</a> |"
    for i = 1 to totpages
    .write "<a href = '#' onclick = 'numberedpage(" & i& ")'>" & i & "</a> | "
    next
    .write "<a href = '#' onclick = 'nextpage'>Next</a>"
    end with
    end sub

    sub populatetable(pagenum)
    startpage =(pagenum-1)*numperpage
    linenum = 0
    for i = startpage to (startpage + numperpage-1)
    for j = 0 to numcols
    if i <= numrows then
    document.all.otbody.rows(linenum).cells(j).innerht ml = alldata(j,i)
    else
    document.all.otbody.rows(linenum).cells(j).innerht ml = blank
    end if
    next
    linenum = linenum + 1
    next
    end sub

    sub previouspage
    if pagenum > 1 then
    pagenum = pagenum - 1
    call populatetable(pagenum)
    end if
    end sub

    sub nextpage
    if pagenum < totpages then
    pagenum = pagenum + 1
    call populatetable(pagenum)
    end if
    end sub

    sub numberedpage(i)
    pagenum = i
    call populatetable(pagenum)
    end sub


    //-->
    </script>
    </head>

    <body>

    <hr width = "700"/>
    <br />
    <script language="VBScript" type="text/vbscript">
    <!--

    call dbconnect

    pagenum = 1

    call setuptable(numrows,numcols)

    call closedb

    call populatetable(pagenum)
    //-->
    </script>
    </body>

    Thanks David

  12. #12
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •