Link to home
Start Free TrialLog in
Avatar of farnsworth
farnsworth

asked on

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?
Avatar of pique_tech
pique_tech

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.
Avatar of farnsworth

ASKER

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).
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
  );
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
  );
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.
SOLUTION
Avatar of pique_tech
pique_tech

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!
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?
I tried that one also, the query fails completely, not appending any records (new or not).
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.

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!
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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.