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 50

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 84
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.
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.


Author Comment

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

Expert Comment

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

LVL 84
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 84

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 (Access MVP)
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

751 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