How to disable odbc error

[mfauzim]

So far I manage to exract from db A and insert data into db B. My issue now is whenever I insert data into db B, I got odnc dialogbox error:

cannot insert duplicate key row....
I need to press ok,abort on the dialogbox to preceed to next line

I just want to know how to disable/suppress the error dialogbox and continue my script looping. For info I cannot control what is in db A, ,but in Db B it will check for duplicate.

[Marcus Tettmar]

Well, I would first see if that value is in the database and only insert if it isn't. Do an SQL statement that looks for that index value. If it's not there insert, if it is do nothing.

Maybe you can also use "on error resume next" to ignore that error. Remember to put "on error goto 0" afterwards to trap other errors.

[mfauzim]

I've tried the on error but still dialog box appear ( dialog box title: macro Scheduler '

I am avoiding to check whether data already exist, to avoid programming/ and also to reduce time to load data.


regards

[Marcus Tettmar]

Well, I don't think a quick bit of SQL to see if the value exists will add any more overhead than attempting to insert data that can't be inserted and causing the database to respond with an error. In fact it will probably be quicker and *reduce* overhead.

But perhaps there is an ODBC or database setting to suppress such errors - you'd have to check your ODBC driver settings, manual, or with the driver vendor (Oracle).

[pgriffin]

Marcus is (as usual) correct with his statement that you should check the DB before you try to input a duplicate.

it is really very simple.

Open a connection to db A
Open a connection to db B
read a value from A
Select rows from db B where the index-value = "value from A"
if this function returns a record
then do not insert, go back to the first line.....

pseudo -code but the logic is simple....

Let us know if you need a little help with the SQL, it would also be simple to write.

[mfauzim]

Thanks for the suggestion, yes there is no tiem difference..