Quote Originally Posted by TedH View Post
I added an index to the acrSOURCE field and re-ran the test.
It dropped to 30 second.

However the dbOpenDynaset looping method only took 15 seconds!
Long time no DAO-usage for me - dbOpenDynaset seems comparable to adOpenKeyset
(both Flags allowing the same kind of an "only RowID-based enumeration" IIRC -
then expanding to "more Record-Details" only, when you access the Fields-List on a current Record).

So the external loop is apparently using the "most efficient Flag" already, which the engine provides.

Though why on earth the same kind of "efficient looping" is not applied to an internal engine-operation,
which has the advantage to use internal calls (and no need to work through a COM-interface) - that's baffling to me.

Maybe one has to support the engine a bit more, by giving appropriate "hints and stuff" - as perhaps (with Chris's example)
the explicit QueryType - dbQDelete or something... (but normally the engine should switch its operation-state automatically
to "the right thing", whilst parsing the SQL-text -> a Delete is a Delete...

Alternatively one could play around with the several Flag-Values one can pass into the .Execute Method optionally.
But you see, I'm drifting into "guessing territory"...

Quote Originally Posted by TedH View Post
I tried wrapping it with Transactions, but that just runs for a while and stops with an error.
Error 3035 System Resource Exceeded
Seems the whole thing is quite memory intensive then (due to your "lots of records") ... in SQLite one
can adjust, where the DBEngine stores its "Transaction-Journal" (memory, or temp-file) - but not sure,
what DAO-DBEngine-Options one could adjust for stuff like that... hmm, googling a bit...

DBEngine.SetOption dbFlushTransactionTimeout, 0 might worth a test in that regard - but as said, all guessing here...

Olaf