ADODB Command Append Query: Add Only New Values Instead Of Failing

 I have an adodb command that is running an append query.  The first time you run the query the values to be appended are all new and don't violate the key of the receiving table.  When I run the query again after adding a few new records to the source table, I get a key violation (as I should), but the whole query stops. It does not append only the new records as I would like it to. If you run the same query via Docmd.RunSql, it appends only the new records. What do I have to do to get the ADODB Command Append query to bypass the error and append only the new records?
LVL 1
farnsworthAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pique_techCommented:
Can you change your code so that the condition is not violated, i.e.,
      INSERT INTO DestinationTable
      SELECT Field1, Field2, ... , FieldN FROM SourceTable WHERE PrimaryKeyID NOT IN (SELECT PrimaryKeyID FROM DestinationTable)
Then you get the benefit of your original append attempt working as expected because none of the source records are there yet, and subsequent attempts will work too because the duplicates will be removed before the append is attempted.

Just a thought.
0
farnsworthAuthor Commented:
That is usually what I try to do as a work around, but this time in the source table there is just PackageName as a key, but in the destination there is PackageName and MonthEndDate (date is added by query).
0
GreymanMSCCommented:
Ah,  then what you need is the EXISTS statement.

  insert into YourDestination (PackageName, MonthEndDate, AnyOtherData)
  select Q.PackageName, Q.MonthEndDate, Q.AnyOtherData
  from YourSourceQuery As Q
  where not exists (
    select * from YourDestinationTable As T
    where T.PackageName=Q.PackageName and T.MonthEndDate=Q.MonthEndDate
  );
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

GreymanMSCCommented:
Ahem:

  insert into YourDestinationTable (PackageName, MonthEndDate, AnyOtherData)
  select Q.PackageName, Q.MonthEndDate, Q.AnyOtherData
  from YourSourceQuery As Q
  where not exists (
    select * from YourDestinationTable As T
    where T.PackageName=Q.PackageName and T.MonthEndDate=Q.MonthEndDate
  );
0
farnsworthAuthor Commented:
Although I have not used the Not Exists before, I have done the workaround using derived tables before, and that would be a valid solution. The problem is I am trying to teach someone about using the command object who is not all that familiar with SQL and Queries (knows a little bit about queries, but doesn't quite 'get' them). I am really looking for the property or argument I need to pass that will force the Command object to execute the query, ignore the error, and add only the new records (the ones that don't exist in the destination table).  Maybe there is not a way, but that just seems odd to me since you can use Docmd.RunSql in conjuction with Docmd.SetWarning False or the DAO Execute method with dbFailOnError which accomplishes what I am looking for. It seems weird that ADODB would not have the same option.
0
pique_techCommented:
Based on what I can find at Microsoft on the options you can specify to the ADO Command object, I don't think there IS such an option in ADO.  But I've been wrong many times in the past.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcstexecuteoptionenum.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcstcommandtypeenum.asp
0
farnsworthAuthor Commented:
I have been doing a lot of searching and can't find anything either. . .which I am hoping I have just been unable to find it. It just seems to silly to me not to have that option. Thanks for looking though!
0
pique_techCommented:
It occurs to me:  why not capture that error with error handling and just ignore it?  Does that have the desired effect of appending only the "really" new records?
0
farnsworthAuthor Commented:
I tried that one also, the query fails completely, not appending any records (new or not).
0
pique_techCommented:
I'm all fresh out of ADO ideas.  Sorry.

If you're relatively certain you'll never migrate from Access, why not use DAO?  Despite Microsoft touting ADO as the end-all be-all universal data access paradigm, there are some things for which DAO is uniquely (and exclusively) suited.

0
GreymanMSCCommented:
It seems silly to me to HAVE such an option.  A well built query should affect ALL records in the set it targets, and in the same way, OR fail and rollback cleanly when something unexpected occurs.  Relying on it to fail on some of the set, yet still process the rest, in order to do the right thing just because you've built a query with an expected bug is - it's just plain WRONG and nobody should ever do it!
 
Using a derived table is NOT a work-around, it is the way it should be done!
0
farnsworthAuthor Commented:
Unfortunately not everyone has the mastery of SQL to build a 3 table dervied table query with many parameters and many user defined function. This is especially true when you can't use the query builder to contruct the query you have to write it all in SQL.  A few other points. . . Access 2000 continually corrupts Derived Table queries in its query editor (replaces FROM () with FROM []. which doesn't work in code) and also why do most (if not all, I can't think of another one) of Access' process methods (i.e. DAO, Docmd.RunSql, and direct query editor processing) have this feature if it is "just plain WRONG" and "NOT a work-around"? It accomplishes the same result with drastically less setup time and effort. I hope you didn't think I was saying your solution was wrong or bad, for you seem to be up in arms about it. It just didn't fit my needs, nor did it answer my question (it offered a viable alternative method without addressing the subject question). I agree that a derived table is probably the cleanest (although most complicated and difficult) solution.
0
GreymanMSCCommented:
MS access encourages many bad habits in an effort to make things friendlier for the inexperienced user.  Access was originally marketed towards home users and small non-critical systems, but designing the system this way has come back to haunt them as users are demanding more and more reliability from there databases.  

The replacement bracketing of sub queries would work fine, for instance, if MS did not also use square bracketing around field and table names to allow the user to include spaces - while at the same time failing to implement nesting of square brackets into the query parser.  (The left hand obviously did not talk to the right hand when they developed that one.)
 
If the DAO fail on error paradymn wasn't wrong, why do you think it hasn't been mplemented it in ADODB?  They're trying to phase out bad judgement calls grandfathered into the system's design specifications.  Fail on expected error works fine - but failing on an unexpected error corrupts data integrity.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
farnsworthAuthor Commented:
Thanks, that explanation makes it a lot clearer why they didn't include this feature in ADO. I ended up just writing the derived table query for this guy, and once he get a little more familiarity with SQL I will explain how it is working.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.