Jim Lawrence (AccessD)
accessd at shaw.ca
Fri May 28 15:49:21 CDT 2004
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