Retrieve N records starting at record M

[Kreszch68]

Sometimes you need to have a way to retrieve a number of records, starting at record M.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Of course there is the TOP function in SQL, but this will not enable retrieving a record set beginning at record 100 and ending with record 200.<o></o>
<o></o>
The function RecordsNfromM returns a record set that you can pick up for further processing. For example, you want to retrieve portions of 10 thousand records from a table with 1 million records to pass to Excel. You can use the function to return the records from 1 to 10 thousand and then 10 thousand and one to 20 thousand. Or just say I want record 900 thousand to 910 thousand. <o></o>
The good thing is you can use existing query string functions (as long as they are valid within the <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:City><st1lace>ADO</st1lace></st1:City> context). <o></o>
Take in mind that I’ve designed this for Access. I know there are other languages that have native functions to cover this, but I thought it would be nice to have within Access.

The function call:

[LEFT]Option Compare Database
Option Explicit
'Set reference to Microsoft ActiveX Data Objects 2.x Library
Public blnHasRecords As Boolean
'Coded by Johan Kreszner
'Mio-Software Netherlands
Public Sub TriggerNfromM()
'Fetch N records from a recordset starting at record M
'Use any valid SQL string function to pass to the function
Dim lStartFromRecord As Long
Dim lNumberOfRecords As Long
Dim sSQLName As String
Dim sOrderByFieldName As String
Dim oRsResult As New ADODB.Recordset
lStartFromRecord = 50000            'The first record you want to return
lNumberOfRecords = 35               'The number of records you want counted from the startfromrecord
sSQLName = "SQL_Example()"          'Mind the () in the string, if you don't add the parenthesises the function can't evaluate the name
sOrderByFieldName = "IdTableY ASC"  'The ordering determines how the collection is returned, so be aware how this affects the results
Set oRsResult = RecordsNfromM(sSQLName, lNumberOfRecords, lStartFromRecord, sOrderByFieldName)
'Test if there is a valid result
If Not blnHasRecords Then Exit Sub 'The cursor can't go further than the last record
'From here you can pick up the recordset and do whatever you need to do with it.
'<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>
'Your code
'<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>
'Test, skip this section, it's just a test to see if you get results.
oRsResult.MoveFirst
Dim arrTest() As Variant
Dim lT As Long
arrTest() = oRsResult.GetRows()
For lT = 0 To UBound(arrTest, 2)
   Debug.Print lT + 1, arrTest(1, lT), arrTest(2, lT)
Next lT
End Sub[/LEFT]

The actual function:​

[LEFT]Public Function RecordsNfromM(ByVal sSQLName As String, _
                             ByVal lNumberOfRecords As Long, _
                             ByVal lStartFromRecord As Long, _
                             Optional ByVal sOrderByFieldName As String) As ADODB.Recordset
'Coded by Johan Kreszner
'Mio-Software Netherlands[/LEFT]
 
[LEFT]'sSQLName =  name of function that returns the string to construct the SQL-Statement
'sOrderByFieldName = name of field used to order the records, if not passed records are ordered according
'to the ordering of the SQL-statement passed to the function (original order)
'Also Desc or Asc are passed with sOrderByFieldName ("fieldname ASC")
'NOTE: Records are always first ordered by the field set in the SQL-Statement you pass
Dim oCn As New ADODB.Connection
Dim oRs As New ADODB.Recordset
Dim vBookMarks() As Variant
Dim lRecCount As Long
Set oCn = CurrentProject.Connection
With oRs
       'Debug.Print "StartOpeningRecordset" & vbTab & Now()
       .CursorLocation = adUseClient
       .Open Eval(sSQLName), oCn, adOpenStatic, adLockReadOnly
       'Debug.Print "RecordsetOpen" & vbTab & Now()
       .Sort = sOrderByFieldName
       'Debug.Print "RecordsetSorted" & vbTab & Now()
       .MoveFirst
       'Debug.Print "RecordsetToFirst" & vbTab & Now()
              'Check if user doesn't ask for a record beyond the number of total records
              If lStartFromRecord > .RecordCount Then 'From record number higher than total number of records
                      MsgBox "The number of the first record asked is beyond the total number of records", vbCritical
                      blnHasRecords = False
                      Exit Function
              'Check if the total of records asked can be retrieved, if not return until last record in recordset
              ElseIf lStartFromRecord + lNumberOfRecords > .RecordCount Then 'Can't retrieve more than max number of records
                       lNumberOfRecords = .RecordCount - lStartFromRecord
                       MsgBox "Can't retrieve more than " & lNumberOfRecords & "  records", vbExclamation
              End If[/LEFT]
 
[LEFT]      .Move (lStartFromRecord)
      'Debug.Print "RecordsetToFirstAsked" & vbTab & Now()
      ReDim vBookMarks(lNumberOfRecords - 1) 'Dimension the array for the bookmarks
      'Set the bookmarks for the records
      For lRecCount = 0 To UBound(vBookMarks)
          vBookMarks(lRecCount) = .Bookmark
          .MoveNext
      Next lRecCount
      'Debug.Print "BookmarksCreated" & vbTab & Now()
      .Filter = vBookMarks() 'filter the recordset by the bookmarks
      'Debug.Print "RecordsFiltered" & vbTab & Now()
      Set RecordsNfromM = oRs
      blnHasRecords = True
      'Debug.Print "Function ready" & vbTab & Now()
End With
End Function[/LEFT]

Sample of query string function:​

[LEFT]Public Function SQL_Example()
   SQL_Example = "SELECT IdTableY, TableYName, TableYDescription FROM TableY"
End Function[/LEFT]

 

[SydneyGeek]

Johan,

Thanks for posting this. I'm sure that quite a few members will find it useful.

Denis