This approach seems a bit strange, sorry to be so direct, but why on earth do you open a form to run some code, then close it? And this within a loop?
Why not place all your code in a module, then run it there? This would give you much more control over the process - and (my opinion, sure) get rid of that macro thingie, convert it to VBA, so you can have some control over it.
I'm guessing (or close to betting), that somewhere along this rather convoluted route, there are memory leaks, some objects which are not closed or properly released...
I don't think I'd go late bound on DAO, but rather ensure there's a reference to DAO (in VBA - Tools | References), then use
[tt] Dim CostGrids_temp As DAO.Recordset
Set CostGrids_temp = dbs.openrecordset("CostGrids_temp")[/tt]
Same with the openquery stuff, I'd probably rewrite it to execute the queries in VBA. Not that I use DAO much, but basically I think something like fetching the query (assigning to a querydef object), resolve parameters if any, then execute, should be easily found through a search.
There's no need to use [brackets] around variable names.
[tt] [CostGrids_temp].AddNew
' should/could be
CostGrids_temp.AddNew[/tt]
I wouldn't be surprised if more of what you're doing here, could be performed through executing queries, in stead of using recordsetapproaches. Executing queries are more effective.
If the recordsetapproach is really necessary, then stuffing it all into one module, would enable you to keep the recordset(s) open, in stead of opening/closing all the time, which may be one of the reasons for the current challenge. Especially if there are issues with regards to the objects not being properly closed/released.
Roy-Vidar
