[AccessD] Paging Recordsets

Charlotte Foust cfoust at infostatsystems.com
Fri May 28 17:20:10 CDT 2004


Have you tested this, Jim?  I thought the .Clone method was for DAO
recordsets.    You can't just pass an ADO recordset to a DAO recordset
unless they've changed something dramatically in the latest MDAC.

Charlotte Foust

-----Original Message-----
From: Jim Lawrence (AccessD) [mailto:accessd at shaw.ca] 
Sent: Friday, May 28, 2004 12:49 PM
To: Access Developers discussion and problem solving
Subject: RE: Re: [AccessD] Paging Recordsets


Hi Paul:

To step through a group of records so many at a time try something like

Public Function GetRecords(optional intCount as Integer = 0, _
				   optional lngPos as long = 0, _
				   optional intDirection as integer = 1)
as ADOB.Recordset  Static lngPosition as long  Static intNumberofRecords
as integer  Dim strSQL as String

 'You can also just close the caller recordset as the result is a clone
and  'closing one closes the other.  If Not GetRecords Is Nothing Then
GetRecords.Close: Set GetRecords = Nothing

 if intCount <> 0 then intNumberofRecords = intCount
 if lngPos <> 0 then lngPosition = lngPos

 ' intDirection = 1 or intDirection -1
 intDirection = intDirection Mod 2
 if intDirection = 0 then intDirection = 1

 lngPosition + (lngPosition * intDirection)
 if lngPosition < 0 then lngPostion = 1

 strSQL = "SELECT top " & str(intNumberofRecords) & _
	    " * FROM  MyTable where ID >= " & str(lngPosition)

 Set GetRecords = New ADODB.Recordset
 With GetRecords
   .ActiveConnection = MyActiveConnection
   .Source = strSQL
   .CursorLocation = adUseClient
   .Open , , adOpenDynamic, adLockOptimistic
 End With

End Function

Then to call the function like:

intNumberofRecordstoRetrieve = 25 'Records for display
lngWheretoStartIntheFile = 5000 'Start at this position intReadDirection
= 1 'Forward

'To initialize
set rsMyRecordset = GetRecords(intNumberofRecordstoRetrieve,_
					lngWheretoStartIntheFile,_
					intReadDirection).Clone

'To continue
set rsMyRecordset = GetRecords().Clone
...

With rsMyRecordset
  If .BOF = False Or .EOF = False Then .MoveLast:.MoveFirst
End With

...

My apologies but I just wrote this code from memory as there is limited
error checking, testing functionality or typos, a complete set of
defined variables. The concept is right but I could not find my original
code...but it should be close. In the code it assumes your primary key
is name 'ID'. It only brings back as many records as you request and
starts at any position in the table(s)

HTH
Jim


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of
paul.hartland at fsmail.net
Sent: Thursday, May 27, 2004 2:31 AM
To: Access Developers discussion and problem solving
Subject: Re: Re: [AccessD] Paging Recordsets


Martin,
Stumped on this one the only way I can think of doing something similar
is create a temporary table, write the result set into the temporary
table and have an additional counter field 1 to ??? then with an access
query have something like SELECT * FROM tblTemp WHERE
tblTempCounterField>=1 And tblTempCounterField<=100

then change the query to
SELECT * FROM tblTemp WHERE tblTempCounterField>=101 And
tblTempCounterField<=200

etc etc as the user presses the next or previous page button. Paul
Hartland PS if you come across a better solution I would like to know
how to do it for future reference.





Message date : May 26 2004, 07:33 PM
>From : "Martin Reid"
To : "Access Developers discussion and problem solving"
Copy to :
Subject : Re: [AccessD] Paging Recordsets
I know. I want to to the same thing with data from SQL Server in Access.

Martin


----- Original Message -----
From: "MartyConnelly"
To: "Access Developers discussion and problem solving"

Sent: Wednesday, May 26, 2004 6:40 PM
Subject: Re: [AccessD] Paging Recordsets


> Paging is quite commonly done on ASP websites
>
> You just set or use three properties of the ADO recordset
> ' Create recordset and set the page size (no of records per page) Set 
> objPagingRS = New ADODB.Recordset objPagingRS.PageSize = iPageSize
> ' Get the count of the pages using the given page size
> iPageCount = objPagingRS.PageCount
> ' Move to the selected page number
> objPagingRS.AbsolutePage = iPageCurrent
>
> Here is some quickly cobbled sample code stolen from
http://www.asp101.com
>
> Search on "paging" as there several methods available including 
> placing in an array There are probably articles floating around as to 
> best method for performance
>
>
> Sub AdoPaging()
> 'stolen from 
> http://www.asp101.com/samples/viewasp.asp?file=db%5Fpaging%2Easp
> ' BEGIN USER CONSTANTS
> Dim CONN_STRING As String
> Dim CONN_USER As String
> Dim CONN_PASS As String
>
> CONN_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=C:\Program Files\Microsoft 
> Office\Office\Samples\Northwind.mdb;" & _ "User Id=admin;" & 
> "Password="
>
> ' Our SQL code - overriding values we just set
> ' Comment out to use Access
> 'CONN_STRING = "Provider=SQLOLEDB;Data Source=10.2.1.214;" _ ' & 
> "Initial Catalog=samples;Connect Timeout=15;" _ ' & "Network 
> Library=dbmssocn;" 'CONN_USER = "samples"
> 'CONN_PASS = "password"
> ' END USER CONSTANTS
>
> ' Declare our vars
> Dim iPageSize As Long 'How big our pages are
> Dim iPageCount As Long 'The number of pages we get back
> Dim iPageCurrent As Long 'The page we want to show
> Dim strOrderBy As String 'A fake parameter used to illustrate passing 
> them Dim strSQL As String 'SQL command to execute
> Dim iRecordsShown As Long 'Loop controller for displaying just
> iPageSize records
> Dim I As Long 'Standard looping var
> Dim objPagingConn As ADODB.Connection
> Dim objPagingRS As ADODB.Recordset
>
> ' Get parameters
> ' set number of records per page
>
> iPageSize = 10 ' You could easily allow users to change this
>
> ' Set page to show or default to 1
>
> iPageCurrent = 2
>
>
> ' If you're doing this script with a search or something
> ' you'll need to pass the sql from page to page. I'm just
> ' paging through the entire table so I just hard coded it.
> ' What you show is irrelevant to the point of the sample. 'strSQL = 
> "SELECT * FROM sample ORDER BY id;"
>
> strOrderBy = "'Customer ID'"
> 'strOrderBy = "last_name,first_name,sales"
>
> ' Build our SQL String using the parameters we just got. strSQL = 
> "SELECT * FROM customers ORDER BY " & strOrderBy & ";"
>
>
> Debug.Print strSQL
>
> ' Now we finally get to the DB work...
> ' Create and open our connection
> Set objPagingConn = New ADODB.Connection
> 'objPagingConn.Open CONN_STRING, CONN_USER, CONN_PASS 
> objPagingConn.Open CONN_STRING ' Create recordset and set the page 
> size Set objPagingRS = New ADODB.Recordset
> objPagingRS.PageSize = iPageSize
>
> ' You can change other settings as with any RS 
> 'objPagingRS.CursorLocation = adUseClient objPagingRS.CacheSize = 
> iPageSize
>
> ' Open RS
> objPagingRS.Open strSQL, objPagingConn, adOpenStatic, adLockReadOnly, 
> adCmdText
>
> ' Get the count of the pages using the given page size iPageCount = 
> objPagingRS.PageCount
>
> ' If the request page falls outside the acceptable range,
> ' give them the closest match (1 or max)
> If iPageCurrent > iPageCount Then iPageCurrent = iPageCount If 
> iPageCurrent < 1 Then iPageCurrent = 1
>
> ' Check page count to prevent bombing when zero results are returned! 
> If iPageCount = 0 Then Debug.Print "No records found!"
> Else
> ' Move to the selected page
> objPagingRS.AbsolutePage = iPageCurrent
>
> ' Start output with a page x of n line
>
> ' Show field names in the top row
> Dim strtitles As String
> strtitles = ""
> For I = 0 To objPagingRS.Fields.Count - 1
> strtitles = strtitles & objPagingRS.Fields(I).Name & " "
> Next 'I
> Debug.Print strtitles & vbCrLf
>
> ' Loop through our records and ouput 1 row per record iRecordsShown = 
> 0 Dim strFields As String
> strFields = ""
> Do While iRecordsShown < iPageSize And Not objPagingRS.EOF
>
> For I = 0 To objPagingRS.Fields.Count - 1
>
> strFields = strFields & objPagingRS.Fields(I) & ","
>
> Next 'I
> Debug.Print strFields & vbCrLf
> 'clear strfields shown
> strFields = ""
>
> ' Increment the number of records we've shown
> iRecordsShown = iRecordsShown + 1
> ' Can't forget to move to the next record! objPagingRS.MoveNext
> Loop
>
> ' All done - close table
>
> End If
>
> ' Close DB objects and free variables
> objPagingRS.Close
> Set objPagingRS = Nothing
> objPagingConn.Close
> Set objPagingConn = Nothing
>
> End Sub
>
>
> Martin Reid wrote:
>
> >Anyone every done any work on paging large recordsets in Access. I 
> >want
to
> >look at the possibility of breaking up massive recordsets returned 
> >from
SQL
> >Server in page sets of say 100 records. Rather than pull them all at 
> >the start I want the user to be able to select Page 1, Page etc etc 
> >then move freely between pages.
> >
> >Only 100 records (or less) would be taken of the server at one time.
> >
> >I cannot use filters at the client end. All records MUST be 
> >available?
> >
> >MArtin
> >
> >
> >
>
> --
> Marty Connelly
> Victoria, B.C.
> Canada
>
>
>
> --
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>

--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--

Whatever you Wanadoo:
http://www.wanadoo.co.uk/time/

This email has been checked for most known viruses - find out more at:
http://www.wanadoo.co.uk/help/id/7098.htm
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list