Solved

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

Posted on 2014-01-01
9
1,636 Views
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 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.
0
Comment
Question by:NNOAM1
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 49

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.

/gustav
0
 

Author Comment

by:NNOAM1
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.
0
 
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.
0
 

Author Comment

by:NNOAM1
ID: 39749668
The main error is duplicate key violations.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 49

Expert Comment

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

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

/gustav
0
 
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.
0
 

Author Comment

by:NNOAM1
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?
0
 
LVL 84

Accepted Solution

by:
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.
0
 
LVL 47

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.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now