Problem with Sequential Transactions

[TorrediPisa]

Hello People

I have made a VB application where I want to delete a certain numbers of records from a .Mdb Table and then to insert some others. In practice I have two sequential transactions:
(let's suppose an ADO Connection)
cnADO.execute “DELETE FROM TBL1 WHERE FLD_1 = ‘X’”
cnADO.execute “INSERT INTO TBL1 (FLD1, FLD2, ...) VALUES ..

Here is what happens: if the first execute lasts a bit, the second execute starts and begins to insert data which are deleted by the first execute still running.
The final result is that some of the records inserted are indeed deleted.

Could you suggest a good solution for this problem?
Thank you very much for yr help.

Regards
TdP

 

[Golom]

You may try BeginTrans ... CommitTrans and I would insert a DoEvents to allow the database to complete what it's doing.

cnADO.BeginTrans
cnADO.execute “DELETE FROM TBL1 WHERE FLD_1 = ‘X’”
cnADO.CommitTrans
DoEvents
cnADO.BeginTrans
cnADO.execute “INSERT INTO TBL1 (FLD1, FLD2, ...) VALUES ..
cnADO.CommitTrans

 

[petermeachem]

This might be another manifestation of the Access write cache delay thing.

http://www.tek-tips.com/viewthread.cfm?qid=910204