Dealing with large recordsets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Personally, I think the better approach would be to find out why it is
"crashing" (whatever you mean by that).

Does it fail with an error? (If so, what is the error?) Or does it
simply come to a halt? (If so, on what statement?)

A million records is a lot. But it should still work fine, as long as
the tables are indexed correctly, and the SQL and VBA code is wirtten
efficiently.
 
Back
Top