Re: Dealing with large recordsets

I've been thinking about your recordset problem and I threw together some code.

The code assumes you have a field named "MyRecID" of type Long in table "DataDump". "MyRecID" is the field that is numbered to be able to select groups of records instead of all the records.

This is AIR CODE!!

Watch for line wrap...

'**************beg code****************
Private Sub Command2_Click()
Dim i As Integer, k As Long
Dim rst As DAO.Recordset
Dim db As Database
Dim strSQL As String
Const w = 250000

Set db = CurrentDb
k = 0

'select MyRecID and set sort order for records to be numbered
'>>>> change the ORDER BY field(s) to reflect your order requirements
strSQL = "Select MyRecID From DataDump Order by Field3, Field4"
Set rst = db.OpenRecordset(strSQL)

' check for records
If rst.BOF And rst.EOF Then
MsgBox "No Records found"
rst.Close
Set rst = Nothing
Exit Sub
End If

rst.MoveFirst

' loop thru RS and renumber records
With rst
Do While Not .EOF
k = k + 1
'.Edit
!MyRecID = k
.Update
If Not .EOF Then
.MoveNext
End If
Loop
End With

'done renumbering - close RS
rst.Close

'select the fields and limit the records to 250000
'>>>> change this line to your fields
strSQL = "Select Field1, Field2, ..., Field3"
'>>>>
strSQL = strSQL & " From DataDump"
strSQL = strSQL & " Where MyRecID Between " & i * w & " and " & (i * w) + w
strSQL = strSQL & " Order by MyRecID"

'get records in groups of Recordcount/250,000
For i = 0 To (k / w)
'open recordset
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

'we know there are records,
'but it is always good to check :)
If Not (rst.BOF And rst.EOF) Then

'#####################
'
'run queries and/or calculations
'
'#####################

End If

'done with this group of records
'close RS
rst.Close

Next

Set rst = Nothing
Set db = Nothing
End Sub
'**************end code****************

HTH
--
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

Johnny Bright wrote:
Hi there,

I have a large table, up to 1 million records. I run a series of queries on this table to create what is essentially a pivot table in Access 2000. When I do this, the process often crashes. What I want to do is process 250,000 records at a time and so build my final data table. I'm thinking of something like using the RecordCount property for this, as in:

set rst = db.OpenRecordset("DataDump", dbOpenSnapshot)
rst.Move Last
if rst.RecordCount > 250,000 then

rst.RecordCount 1 to 250,000 ..... run my queries

if not rst.EOF

then rst 250,000 to 500,000 .... run my queries etc.

I have also thought of inserting an auto number field, in code, into my DataDump table and using these numbers, ie:

With rst.lngRecordID (my autonumber field) 1 to 250,000 ... run my queries etc

Which of these would be best or can anyone think of a better idea.

Thanks,

All help greatly appreciated.

JR
.

Relevant Pages

  • Re: Forcing a ReQuery
    ... queries 1 and 2, which you say works correctly, or in the fact that queries ... > Dim dbs As DAO.Database ... > Set rst = qdf.OpenRecordset ... > Set qdf = Nothing ...
    (microsoft.public.access.modulesdaovba)
  • DAO Querydefs
    ... My problem is that a lot of these queries use ... Set Rst = .OpenRecordset ... Dim Qdef As DAO.QueryDef ...
    (comp.databases.ms-access)
  • Need Help | Rolling out new App. | Trouble with some computers.
    ... Dim varReturn As Variant ... Dim lngAuth As Long, strDept As String, strFirst As String, strLast As ... Set rst = db.OpenRecordset ... ' Data file not found in application folder - try to ask the user ...
    (microsoft.public.access.modulesdaovba)
  • Re: Requerying ComboBox in NotInList event
    ... Response As Integer) ... Dim rst As Recordset ... Set rst = CurrentDb.OpenRecordset ... Dim lngBlankFound As Long ...
    (comp.databases.ms-access)
  • Re: Problem with multiselect list box code
    ... Set rst = db.OpenRecordset ... of the listbox but seperate fields on the form EventID & ... Dim frm As Form, ctl As Control ... Dim varItem As Variant ...
    (microsoft.public.access.formscoding)