GROUP query run from excel VBA

[Benjaminvh]

Hi

I have written VBA code in excel that takes an existing access table (1), does calculations and pastes the 'answers' in a different table (2). Through this process, for each record in the first table, about 20 are created in the second. Access cant handle all the records in table2, so what I'd like to do, every now and again (after, say 50000 table1 records/1mil table2 records), is group by a few variables, and sum the calculated column. This will greatly reduce the number of records to something manageable.

I will make an empty table with the required columns, and then group and append each time until EOF (table1). How do I do this using VBA in excel? The SQL text I have is:
INSERT INTO table2 ( PRODUCT_CODE, IRP_MKR, PRM_INC_RATE, SUM_ASSURED_BAND, DURATION, SumOfEXPOSURE )
SELECT table1.PRODUCT_CODE, table1.IRP_MKR, table1.PRM_INC_RATE, table1.SUM_ASSURED_BAND, table1.DURATION, Sum(table1.EXPOSURE) AS SumOfEXPOSURE
FROM table1
GROUP BY table1.PRODUCT_CODE, table1.IRP_MKR, table1.PRM_INC_RATE, table1.SUM_ASSURED_BAND, table1.DURATION;


Any assistance would be greatly appreciated!!!

 

[Benjaminvh]

Hi

I should add that it may be easier to call a stored procedure. So I can save the query in access, and then simply get excel to run it after 50000 records, and so on through the loop.

Thanks!!