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 C. Holley wrote:

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


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.

--
Marsh
MVP [MS Access]

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


Marshall Barton wrote:
> David C. Holley wrote:
>
>
>>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.
>
>
>
> 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.
>

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.

--
Message posted via http://www.accessmonster.com

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

Mark via AccessMonster.com wrote:
> 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.
>