no index violation error?

[YisMan]

i have an append query that sometimes violates the index of the INTO table.
what id like to have is that Access should throw an error at each violation
so that i can handle them. but the notification of the error is from
"setwarnings" not VBA, so how can i handle it?
the query is run through the docmd.runsql statement

 

[Brian]

Have you tried this?

DoCmd.SetWarnings False
DoCmd.RunSQL whatever
DoCmd.SetWarnings True

This should bypass the warning but still allow the system to generate its
error message.

What specific error are you getting that relates to the index? Most errors I
see on append are duplicate key or validation errors. In duplicate key cases,
I just do an unequal join to ensure that duplicates are either not appended,
or function as updates instead of appends. On validation errors, I pre-query
the data to ensure that it has all necessary fields completed and validated
and generate a report or message box to identify the source for the user.

 

[YisMan]

thanks for your help, brian
i am aware that those 2k+ records are violating a unique index, and it is
intentional. meaning, the reason i set up that index is to filter out
duplicates.

what i want to do is:
run the query and as it encounters a duplicate, it should raise an error so
i can paste it into a "duplicates" table for future reference.

i saw some ideas for trappping the error in the form_error handler but this
neither works, as the error is never raised.
thanks again

 

[Tim Ferguson]

what id like to have is that Access should throw an error at each
violation so that i can handle them. but the notification of the error
is from

I would guess that the only way of doing this is one record at a time.
The bulk method

jetSQL = "SELECT etc INTO somewhere FROM something"
db.Execute jetSQL, dbFailOnError

will, AFAIK, completely fail if there are any errors. You may need
something like


jetSelect = "SELECT etc FROM something"
set rs = db.openrecordset(jetSelect, dbopensnapshot, dbforwardonly)

do while not rs.eof
jetInsert = "INSERT INTO somewhere (etc) VALUES "

on error resume next

db.execute jetInsert & "(" & rs!etc & ")", _
dbFailOnError

if err.number <> then msgbox "Failed on """ & rs!etc & """"

on error goto 0

rs.Movenext
loop


But it's probably even better to create an unmatched records query on the
two tables and use the results of that to insert into the table -- avoid
the errors rather than trying to recover from them.

Hope it helps


Tim F

 

[Brian]

An approach that I use on a regular basis is to create two queries, each with
an unequal join on the field that has the duplicates, and then use this to
first append the duplicate records to another table, and then to append
non-duplicates to the target table. I think this beats relying on an error to
flag the duplicates. If you post your e-mail address (do it cryptically by
inserting _nospam_ or something into the address, I will send you an example,
which will be much faster than trying to explain it verbosely here.