Trying to use memory to make processing quicker | Access forum

Hi Anthony

I am trying to use your suggestion of using Arrays to do calculations in memory rather than using recordsets.

For example I have an Array with 2 fields. The first field is Part Number and the second field is a Number (eg Qty). I have another table of part numbers and quantities and I want to add the 2 quantities together into the first Array. So I have also copied the data from the second table into a second Array.
How do I now get the Arrays to reference each other correctly without having to nest 2 loops. There are about 30,000 records in the second Array and 300,000 records in the first. Nesting 2 loops takes for ever.
If I do this as a normal query with the part numbers linked it takes only a few seconds, but looping round each record in the code (even with Exit For once it has found a match) takes almost 7 minutes.
Is there some way to run SQL on the 2 Arrays or something else to make the process much quicker?

Option Compare Database
Option Explicit
Public DataArray() As Variant
Public DataArray2() As Variant

Sub testgrossfcst()

Dim dbData As DAO.Database
Set dbData = CurrentDb

Dim strSQL As String
Dim LngCount As Long, LngCount2 As Long

' Get table XREFERENCE_PREPROD into recordset rstXREF_PRE
' This table has over 300,000 records
strSQL = "SELECT * FROM XREFERENCE_PREPROD"
Dim rstXREF_PRE As DAO.Recordset
Set rstXREF_PRE = dbData.OpenRecordset(strSQL)
rstXREF_PRE.MoveLast
rstXREF_PRE.MoveFirst

'Create Array with number of records to match rstXREF_PRE
ReDim DataArray(1 To rstXREF_PRE.RecordCount, 1 To 2) As Variant

'Add each record from XREF_PRE into the Array
For LngCount = 1 To rstXREF_PRE.RecordCount
DataArray(LngCount, 1) = rstXREF_PRE.Fields("CP_REF_X")
DataArray(LngCount, 2) = 0
rstXREF_PRE.MoveNext
Next LngCount

'Clear out the recordset no longer needed
Set rstXREF_PRE = Nothing

'Get table aeoq_drp orders into recordset rstAEOQ_DRP
'this table has over 30,000 records
strSQL = "SELECT * FROM [aeoq drp]"
Dim rstAEOQ_DRP As DAO.Recordset
Set rstAEOQ_DRP = dbData.OpenRecordset(strSQL)
rstAEOQ_DRP.MoveLast
rstAEOQ_DRP.MoveFirst

'Create Array ready for taking contents of recordset
ReDim DataArray2(1 To rstAEOQ_DRP.RecordCount, 1 To 2) As Variant

'Copy contents of recordset into Array2
For LngCount = 1 To rstAEOQ_DRP.RecordCount
DataArray2(LngCount, 1) = rstAEOQ_DRP.Fields("SOURCE_REF_O")
DataArray2(LngCount, 2) = rstAEOQ_DRP.Fields("SumOfGOOD_QTY_O")
rstAEOQ_DRP.MoveNext
Next LngCount

'Update Array by adding the bom qty from Array2 to the existing value
'THIS IS THE SECTION WHICH TAKES AGES!!!
Dim starttime As Date
starttime = Time()

For LngCount2 = 1 To UBound(DataArray2, 1)
For LngCount = 1 To UBound(DataArray, 1)
If DataArray(LngCount, 1) = DataArray2(LngCount2, 1) Then
DataArray(LngCount, 2) = DataArray(LngCount, 2) + DataArray2(LngCount2, 1)
Exit For
End If
Next LngCount
Next LngCount2
Dim stoptime As Date
stoptime = Time()

'Clear out the recordset no longer needed
Set rstAEOQ_DRP = Nothing

Stop

End Sub