CurrentDb.Execute? CurrentProject.Connection.Execute? DoCmd.OpenQuery? DoCmd.RunSql? DAO.Recordset?

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?
Does CurrentProject.Connection.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.
NNOAM1Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
As long as you can match the newly created records back to the source, that would work - that is, you can compare FieldA, FieldB and FieldF in those tables to determine which were not inserted. You wouldn't really be able to determine why each of those failed, perhaps, but at least you'd know the ones that did.
0
 
Gustav BrockCIOCommented:
If you wish the option to record success or fail for every inserted record, you will have to use VBA where you loop through the source query and inserts every record using .AddNew and .Update in the target table. If .Update fails you can catch the error.

A bit slow perhaps but solid.

/gustav
0
 
NNOAM1Author Commented:
Too slow. Yesterday I tested the Recordset way and the CurrentDb.Execute way on the same data in a table of 250000 records. 17 minutes with the recordset isn't tolerable. 5 seconds for the same table in CurrentDb.Execute! We can't afford waiting for 17 minutes. There has to be something that can be done to make it better.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Not if you want to catch errors on a line-by-line basis. To do that, you must process each line individually, and that's gonna take time.

How about validating the data before you attempt the insert? I'm not sure exactly what's failing, or what sort of errors you're trying to catch, so I don't know if that would work, but perhaps you could determine which lines are valid, and which are not, and then only INSERT those with Currentdb.execute, and then alert the user to the invalid ones and let them make changes.
0
 
NNOAM1Author Commented:
The main error is duplicate key violations.
0
 
Gustav BrockCIOCommented:
Then perhaps you could run a combined update/append query:

    http://www.experts-exchange.com/Q_23594743.html

/gustav
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you pre-validate that? By that, I mean can you determine which of the incoming records will violate the key, and not attempt to insert those? I do this regularly for several of my clients, who import huge numbers of records which must be validated prior to insertion.

Are the violations occurring because of the insert - by which, I mean that you insert record1, and then down the line you insert Record100, and Record100 conflicts with Record1 (a record that did not exist prior to the insert beginning).

Either way, you can determine the validity of your data beforehand, it's just that if you have violations that occur because of records being inserted in the same session things can get a bit more tricky.

The first step is to determine exactly which fields are throwing the violation. Once you do that, you can then examine the incoming data to determine which records would contradict those keys/constraints. If you can identify those, you can write code to exclude them.
0
 
NNOAM1Author Commented:
Maybe I'll check after the CurrentDb.Execute "INSERT INTO....." (with NO dbFailOnError) operation has finished? a quick comparison between the source table and the target table by a "find unmatched" query (like there is in the query wizard). If there are records in the query results, so some data hasn't been inserted and we can see exactly what this data is.
What do you think?
0
 
Dale FyeCommented:
I prefer to use Scott's technique of doing the checks before doing the insert.  I generally create a [Flag] (yes/no) and [Flag_Comment] (text or memo) field in those staging tables and run my "error" checks before doing the insert.  If I identify a record with an issue, I set the flag and add to the comment.  Run through all the checks for the table and then present the user with a list of the flagged records, to fix.  

At this point, you could simply do the insert of the un-flagged records and come back to the flagged ones, but if you are doing this with several tables, you may have referential integrity issues of needing records in table1 before you can do the insert process for table2.

This will take some effort.  Good luck.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.