We have an Access 2007 runtime accde application with an MDB backend, that needs to run many "INSERT INTO.... IN 'AnotherDatabase_path' SELECT * FROM...;". There are a hundred tables, each with hundreds of thousands of records.
Using a Loop in a DAO.Recordset seems to be the worst in terms of time: it takes way too long.
Using CurrentDb.Execute seems to be the quickest way to accomplish the task. But neither using dbFailOnError nor not-using dbFailOnError are good for me.
My aim is to manage errors this way: if an error occurs, I want to write its details in a table and continue with the rest of the records. After all these bulk inserts the user will be able to see all the details about errors that have occured. dbFailOnError rolls-back my whole execute and doesn't enable me to continue. Not-using-dbFailOnError doesn't enable me to know about the details of the errors which has occured. CurrentDb.RecordsAffected is useless - the number itself doesn't tell exactly what has happened. In http://www.utteraccess.com/wiki/index.php/RunSQL_vs_Execute
there is a note at the bottom about OpenQuery: "...One more potential reason to use OpenQuery is when you want to insert dirty data into a temporary table for staging and a running query may have some errors (e.g. duplicate key violation) but you'd rather get the partial result in as opposed to failing the whole operation and getting no data at all."
Can anyone tell me what that final note about OpenQuery means?
Execute have something better to offer? or DoCmd.RunSql? What do you think is the best way to accomplish the task?
I'd appreciate every replies about my dilemma above. Thank you.