Duplicate unique values

[Brad Allison]

I have created a short routine to read certain data from an AS400 and put it
into a small table stored in Access (I know not the best data store, but it
is what we have for now).

Anyway, some of this data coming from the AS400 is duplicating a unique data
column and with the try...catch I get errored out and the sub exits.

Is there a way if a certain error occurs to disregard the added row to the
table and continue the sub instead of exiting out of the sub?

Thanks for any information.

Brad

 

[Jeff Johnson [MVP: VB]]

I have created a short routine to read certain data from an AS400 and put it
into a small table stored in Access (I know not the best data store, but it
is what we have for now).

Anyway, some of this data coming from the AS400 is duplicating a unique data
column and with the try...catch I get errored out and the sub exits.

Is there a way if a certain error occurs to disregard the added row to the
table and continue the sub instead of exiting out of the sub?

Why not:

A) Determine what type of exception is being thrown for this one particular
error and see if it's the same exception as any other data-related error
would throw. If so, find out what identifies this exception as the
"duplicate key" error (or whatever it is).

B) Wrap the statement that updates/adds the data in its own Try/Catch block.

C) In the Catch portion of B), use the results from A) to either test for a
different exception type or interrogate the exception data to see if you've
run into a duplicate key issue or something else. If something else, report
the error and exit the Sub. Otherwise, ignore the error and processing will
continue, effectively ignoring the duplicate data.

 

[Brad Allison]

Thanks, it worked. The err.number happens to be "5" so when it hits that
number, I just have the dataset clear that row.

Thanks for the advice.

Brad