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

Posted on 2014-01-01
Last Modified: 2014-01-01
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 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.
Question by:NNOAM1
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
LVL 51

Expert Comment

by:Gustav Brock
ID: 39749553
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.


Author Comment

ID: 39749565
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.
LVL 85
ID: 39749641
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.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 39749668
The main error is duplicate key violations.
LVL 51

Expert Comment

by:Gustav Brock
ID: 39749680
Then perhaps you could run a combined update/append query:

LVL 85
ID: 39749681
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.

Author Comment

ID: 39749808
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?
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39749825
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.
LVL 48

Expert Comment

by:Dale Fye
ID: 39749910
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.

Featured Post

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question