Determining when an APPEND query fails

  • Thread starter Thread starter David C. Holley
  • Start date Start date
D

David C. Holley

I need to determine when an append query fails to append records to a
table. The data in question is collected remotely using a PPC and then
synch'd into a temporary table in the Access DB via ActiveSync, from
there the data is moved to the main table using an append query. If
there are any key violations, missing values, etc., I need to abort the
append.

David H
 
David said:
I need to determine when an append query fails to append records to a
table. The data in question is collected remotely using a PPC and then
synch'd into a temporary table in the Access DB via ActiveSync, from
there the data is moved to the main table using an append query. If
there are any key violations, missing values, etc., I need to abort the
append.
Click to expand...


Use the Execute method to run the query. It has an optional
dbFailOnError argument to do what you want.

It also set the object's RecordsAffected property so, if the
query runs successfully, you can find out how many records
were appended.
 
Actually, you got me thinking (as well as the DAO vs. ADO thread) about
moving the records via DAO single-row processing as opposed to the
entire batch via SQL. There's some additional processing that's needed
already AND it will allow me to add some polishing to it as well.

David H
 
Along with the dbFailOnError feature of using the Execute method, you might
want to consider wrapping the Execute inside a transaction. This way, if
the append is successful, you can commit the transaction and finish the
append. If there was an error in the append, you can rollback the
transaction (in your error-handling routine) and none of the data will get
appended.

This assures and all-or-nothing append; otherwise, records will be appended
until the error occurs, and the rest won't.
 
I was thinking about approaching it as a transaction, even though I've
never actually used the technique before. I'm liking the idea of doing
things on a record-level since it would make it easier to see the
exceptions and correct them as opposed to having to dig through several
records looking for the problem. Even though the number of records will
be in 20-something range, I'd still like to get the ones that valid out
of the way first.

David H
Come on baby light my fire: www.SpreadFireFox.com
 
Back
Top