Here's the deal. I have an app I made with VBA in Access 97. I recently wrote an import utility for admins to import new marketing data lists into the contact tables. Before doing this the list must be scrubbed against "do not call" and "do not mail" lists. The query I wrote for this is quite simple and ridiculously SLOW when importing large (20,000+) lists (DNC list is about 8,000 records). It goes like this:
docmd.runsql "UPDATE [newdata] set import_flag = 'IMPORT ERROR - DNC MATCH' WHERE phone IN (SELECT phone FROM [main_contact])"
works fine but incredibly slow. It took about 35 minutes to crunch a 20,000 record list.
How can I speed this up?
Would using recordsets be faster?
Any suggestions appreciated.