On Access application when click on Exit button generates SQL Lock

[Ayaz Hoda]

Hi

I've spend lot of time but I can't find an answer how to reslove this lock
issue

When click on exit button creates Lock on SQL
Here's code

* NOTE This code is on Form X after updating values on FormX , Customer Form
Updates Values

Sub Exit_Click()
On Error Resume Next
Dim passed As Boolean
RunCommand acCmdSaveRecord -- Error (it take long time to save)
Me!TotalValue = order_val(Me!MID, Me!Date, passed)
DoCmd.Close acForm, Me.Name -- Error (again it take long time here)
DoCmd.Close
End Sub

On close event

Private Sub Form_Unload(Cancel As Integer)
update_localorder Me!MID
Forms!Customers.SubReport.Requery-- Error (It takes time here as well)
End Sub


On SQL server activity monitor it shows
Command --> Update
Wait type --> LCK_M_X
RID --> 6:1:76550:9

and after 1 or 2 minutes lock reslove itself...

This issue is not 100% consistant, sometimes there's no issue in closing forms

Any suggestion

Regards

Ayaz

 

[Ayaz Hoda]

Solution!

Check Table have dependencies or not
Check what dependencies are i.e view , table
If view then change select statement with No lock

select * from .... WITH (nolock)

Make sure view which linked with access are not updateable from Access
application otherwise create duplicate records if opened or changed by any
other user session.

Thanks