Error handling

[kbsudhir]

Guys,

I get two kind of error when I update my table.

1. When there is duplicate data while updating primarykey.
2. When The database is locked, how to handle both the errors differntly.

In first case I have to I have exit the code.
In second case I have wait then try after sometime.

Please guide

Thanks
Sudhir

:think: :think:

[CreganTur]

1. When there is duplicate data while updating primarykey.

This error is pretty common when you are appending or updating data to an existing table. It just means that some of the values you are updating are duplicate primary key values. Since you can't duplicate primary keys, it just ignores this data.

If you want to get rid of this error message you can use
DoCmd.SetWarnings False
This will turn off all warning message. This is great when you know an annoying and useless error message may pop up.

Now, you MUST use DoCmd.SetWarnings True somewhere before your Sub/Function ends to turn the warnings back on. If you don't then you'll never see another warning again- even the important ones!


When The database is locked, how to handle both the errors differntly.
What's the exact error message you're getting in this case?

[nepotist]

Cregan,

when you turn the warning off does it still update the data?? inspiteof the duplicate primary key??

[CreganTur]

Cregan,

when you turn the warning off does it still update the data?? inspiteof the duplicate primary key??

Yes, all of the data updates EXCEPT the data with the duplicate primary key. The duplicate data is ignored because Access recognises it as duplicate.

Turning off the warning just keeps Access from asking if you're really, really, truly certain that you are aware of the consequences of what you're preparing to do [/sarcasm] (sarcasm aimed at Access... not you.)

[asingh]

Basically setting warning to OFF...will supress all access pop up messages. Even when you delete objects, the warnings will not be shown. Be sure to turn it back ON. I think once you turn it off, even other instances of Access will stop showing the nags.

[Bob Phillips]

For locked record handling, set a retry counter, and using an error handler test if the error is a locked record. If it is, and the retry counter has not been exceeded, set a wait, then retry. It it has been exceeded, time to give up.

[kbsudhir]

But How to make the macro identify the error is due to locked table/database..???

The locked error which I get is written below ( as requested by Randy) -

Run-Time '-2147217887 (80040e21)':
Could not update; currently locked.

[Bob Phillips]

When you get the error, get the value of Err.Number, then you test for that in your code and act accordingly.

[dhartford]

When data is sent from access to excel, number becomes text. e.g.




......

rstExcel("Weight") = rstAccess![TotalWeight]
.......



How to correct it?

Thanks.

[kbsudhir]

If I am not wrong try using the below code.
if your number is integer type then

rstExcel("Weight") = cint(rstAccess![TotalWeight])