Error due to Locking Tables

[srinika]

I am having 2 similar forms with a button. Once the button is pressed, a set of generated data is to be inserted in to a table.
If 2 types of data is to be inserted by the 2 programs, what is the way of avoid crashing one program due to locking of the table?

It would be better if the answer doesn't contain any error traping mechanism.
option Explicit
Dim con as new ADODB.Connection

private Sub Command1_Click()
Dim i as Integer

'"d" is the ODBC name, corresponds to the database having table t1, with fields f1 & f2
con.Open "d"
' this part is in one project
for i = 1 to 5000
con.Execute "Insert into t1 (f1,f2) values(" & i " , 'qqqq')"
next i

' this part is in the other project
' for i = 5001 to 10000
' con.Execute "Insert into t1 (f1,f3) values("& i & " , 'xxxx')"
' next i

con.Close
End Sub


Regards
Srinika

[Cimperiali]

Without error handling it may be quite impossible.
However, transactions may help (even if you may need an "on error resume next"
statement):

con.Errors.Clear
con.begintrans
for i = 1 to 5000
con.Execute "Insert into t1 (f1,f2) values(" & i " , 'qqqq')"
next i
if con.errors.count>0 then
con.rollbackTrans
else
con.CommitTrans
end if
' this part is in the other project
'con.Errors.Clear
'con.begintrans

'for i = 5001 to 10000
'con.Execute "Insert into t1 (f1,f3) values("& i & " , 'xxxx')"
'next i
'if con.errors.count>0 then
' con.rollbackTrans
'else
' con.CommitTrans
'end if





Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, TCartwright, Bruno Paris, Dr_Michael
and all the other wonderful people who made and make Codeguru a great place.
Come back soon, you Gurus.

The Rater