VBA Append Query Key Violations

  • Thread starter Thread starter Steve via AccessMonster.com
  • Start date Start date
S

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
 
Steve via AccessMonster.com said:
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
Click to expand...

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."
 
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.
 
Steve via AccessMonster.com said:
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.
Click to expand...

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 said:
I am using DoCmd.RunSQL.
Click to expand...

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.
 
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
 
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!!
 
Steve via AccessMonster.com said:
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!!
Click to expand...

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