VBA Append Query Key Violations

[Steve via AccessMonster.com]

I am running an append query in Access 2000 VBA. I need to count the number
of records actually appended to the file, as they are appended. I am
getting key violations, which I expected to get due to duplicates. I do not
want the duplicates appended, but I need to be able to subtract from my
counter whenever I get the key violation error. Does anyone know how to
capture the key violation error? I cannot find it in Help or any of my
books. I am new to VBA...
Thanks!
Steve

 

[Dirk Goldgar]

I am running an append query in Access 2000 VBA. I need to count the
number of records actually appended to the file, as they are
appended. I am getting key violations, which I expected to get due to
duplicates. I do not want the duplicates appended, but I need to be
able to subtract from my counter whenever I get the key violation
error. Does anyone know how to capture the key violation error? I
cannot find it in Help or any of my books. I am new to VBA...
Thanks!
Steve

If you use the DAO Execute method to run your append query, you can then
check the RecordsAffected property of the base object to see how many
records were actually appended. For example:

Dim db As DAO.Database
Dim lngAppended As Long

Set db = CurrentDb

With db
.Execute "YourAppendQuery", dbFailOnError
lngAppended = .RecordsAffected
End With

Set db = Nothing

MsgBox lngAppended & " records were appended."

 

[Steve via AccessMonster.com]

Thanks, but I couldn't get this to work. I'm not using DAO. Also, I need to
know after each append whether or not it was successful.

 

[Dirk Goldgar]

Thanks, but I couldn't get this to work. I'm not using DAO. Also, I
need to know after each append whether or not it was successful.

That doesn't give me much to try to help you with. If you are not using
DAO, what *are* you using? And may I suggest that perhaps you *should*
be using DAO? You cannot get the information you want if you use
DoCmd.RunSQL or DoCmd.OpenQuery.

 

[Steve via AccessMonster.com]

I am using DoCmd.RunSQL.

 

[Dirk Goldgar]

I am using DoCmd.RunSQL.

As I said, you cannot get that information using RunSQL. Use DAO
instead. If you tried it and had trouble getting it to work, post the
code you used and the error that occurred, and I'll try to help.

 

[David C. Holley]

As in the earlier post, you'll probably need to go DAO since it supports
transactions which are the key to determining wether or not the query
successeded and if not for way. Also, DAO allows record-level processing
so you should be able to identify easily which records where not
appended (assuming that you're moving records from 1 table to another).
It also allows you to manipulate keys if you need to, I use a PPC to
collect data on the run and then import it. I ran into a problem where
the keys (which were AutoNumbers) began to conflict with the keys in the
master DB. DAO allowed me to omit the primary keys for the parent
records and then update the foreign keys (the corresponding values 564
becomes 783) for the child records.

David H

 

[Steve via AccessMonster.com]

I finally got the DAO code to work. The problem was that I didn't have the
"MS DAO 3.6 Object Library" checked in the Referance listing. I was getting
an error that said "User-defined object not defined". That was very
confusing. Thank you for your help. I REALLY appreciate it!!

 

[Dirk Goldgar]

I finally got the DAO code to work. The problem was that I didn't
have the "MS DAO 3.6 Object Library" checked in the Referance
listing. I was getting an error that said "User-defined object not
defined". That was very confusing. Thank you for your help. I REALLY
appreciate it!!

You're welcome. I'm glad to hear you got it working.