Unanswered: Handle a situation when record is locked
Hi: How to handle a situation when i try to update a record and its lock. Is there any way when its updating a record and its lock the control will be transfer to another SQL insert statement?
' open DEP table in SQL
sqlquery = "SELECT * from tbl_DistList_DEP WHERE Updated = 1"
oDBUtil.OpenADORecordSet sqlquery, cnADOSQL, rsADO, 0, adCmdUnknown
' Update DM data base
Do While Not rsADO.EOF
i = i + 1
' send update query to table dbtr in Debtmaster
sqlquery = "Update dm.Dbtr SET ASSIGN_ID = '" & rsADO!Worklist &
"' " & _
"WHERE DEBTOR_ID = '" & rsADO!Debtor_id & "'"
On Error Resume Next
' save previous and new worklists
sOld = rsADO!Oldworklist
sNew = rsADO!Worklist
' send query to dbtract in debtmaster
sqlquery = "INSERT INTO dm.dbtract (Debtor_id, Comments, User_ID,
act_Date, act_Time) " & _
"SELECT '" & rsADO!Debtor_id & "' as debtor_id,'Assign
ID Changed from " & sOld & " to " & sNew & _
" BatchID = " & sbatchid & "' as Comments, '" &
sUserIDDM & "', getdate() ,'" & GetTimeStamp & "'"
AFAIK, the only valid approach with VB is to use asynchronous database objects.
You declare them withevents.
This allows your app to run in a separate thread from ADO.
You will need to add code to handle the asynchronous events, and add a timer that you'll initialize when you start your query.
Since the two processes are now running in a separate thread, you can monitor the time taken to process the query, and if the time exceeds a set amount, you can cancel the query, and start another one.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert