I am looping through a large Access recordset (200000 records) and performing a process every 50 records. Loop through 50 records, perform process, loop through next 50, perform process, loop through next 50... and so on. I run out of memory after about 150 loops. Should I close the recordset after each loop? What is the correct approach to this? Any help is greatly appreciated by a VBA newbie.

What are you doing that requires this much memory? And how much memory do you have? Where is the processing going on? Is this a desktop solution, client server, SQL server, ? etc.

What are you doing every 50 records? You can also try pulling your data 50 records at a time, then doing your transaction, dump the recordset, and then pull the next 50 etc.



mmcdonal

This is a desktop solution (MS Access.) It loops through 50 records (builds an In-Statement) and inserts it into SQL. The SQL is run against a table via ODBC. Once the query is returned, I go back and get the next 50. It works for awhile but then...

I like the idea of dumping and repulling. What is the best way to accomplish this? rst.close?

I'm not sure I understand.

Loops through 50 records and builds an in-statement. What is an "In-Statement?"

The SQL is run against a table?

You should be dumping the recordsets after each loop, rst.Close, or rst = Nothing. You can leave the connection open.

Can you give me more structure?



mmcdonal

Sorry about the vagueness. It loops through 50 records and builds SQL like this: SELECT Accounts FROM Customers WHERE Account_Number In('1111','2222','3333'). The account numbers are from a local table. The SQL is then run against a data warehouse via ODBC. Our data warehouse can't link to local tables, so I am forced to use this method to query those tables

When I close the recordset after a loop, will it hold my place? For instance if I run through the first 50, then rst.close, what is the best way to pick back up where I left off? How do I start my next loop at record 51?