Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-01-01
9
Medium Priority
?
1,828 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
[X]
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
9 Comments
 
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.

/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 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.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

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

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

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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 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.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

705 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