My program hangs up, hourglass and cpu maxed out

[Guest]

what may be cause this. My below code has no problem with 100 records. But
when doing the same procedure on a recordset of 3500 it hangs up (i see hour
glass and cpu maxed out) and Access becomes non responsive. But when I end
the access session and reopen, I notice the procedure finished. What may
cause this.
I am using 3 dao recordset objects for this procedure ( 2 of them hold
copies of the 3500 records table) and the 3rd is used to addnew duplicates
records to a Duplicates table. Is this a memory problem maybe. I am a newbee!

I am not asking you to look at my code, it does take time and energy but
incase you think you may have the answer if you saw my code I will enclose it:
thanks so much!! newbee

Private Sub ReportDups()

Dim db As DAO.Database
Dim rsGP As DAO.Recordset
Dim rsGC As DAO.Recordset
Dim rsD As DAO.Recordset
Dim ControlGroupNo As String, ThisLoopsGroupNo As String, strName As String
Dim DupGroup As String, ControlGroupProd As String
Dim DupIndex As Integer, LoopIndex As Long, Counter As Long


On Error GoTo ReportDups_Handler

Set db = CurrentDb
Set rsGP = db.OpenRecordset("GroupLookUpIncludingInactive")
Set rsD = db.OpenRecordset("Duplicates")


If Not rsGP.EOF Then rsGP.MoveFirst
Do While Not rsGP.EOF


ControlGroupNo = rsGP!Number
ControlGroupProd = rsGP!Prod

Set rsGC =
db.OpenRecordset("GroupLookUpIncludingInactive")
If Not rsGC.EOF Then rsGC.MoveFirst
Do While Not rsGC.EOF

If rsGC!Number = ControlGroupNo And rsGC!Prod <> ControlGroupProd Then

rsD.AddNew
rsD!Number = rsGC!Number
rsD!Name = rsGC!Name
rsD!Prods = rsGC!Prod
rsD.Update

End If

rsGC.MoveNext
Loop

rsGC.Close
rsGP.MoveNext
Loop


Exit_ReportDups_Click:
rsD.Close
rsGP.Close
db.Close
Set rsGP = Nothing
Set rsGC = Nothing
Set rsD = Nothing
Set db = Nothing

DeleteDups ("Duplicates")
Exit Sub

ReportDups_Handler:

MsgBox Err.Number & Err.Description

End Sub

 

[Van T. Dinh]

I havent looked at your code carefully but it looks like the difference is
10 ^ 2 iterations (i.e. 10K) with 100 records and 3500 ^ 2 (> 12M)
iterations. Thus, the time taken for 3500 Records is 10,000 times the time
required for 100 Records!

 

[MacDermott]

You may want to look at using the wizard to create a "find duplicates"
query.
That would be a lot more efficient than your recordsets.