Trouble Locking Records with ADODC

[Cyberseduct]

I am having too much problems with locking a table or a recordset on a network.I have to upadte 4 tables when doing a save.I tried to trap the errors but it is not working properly.When I test the error trapping code on the network there is garbage being written to tables.I have not been able to solve this problem since 2 weeks. The error trapping code is working correctly with a simple form but in transaction form there is problems.I desperately need help.

Thanks, hope you have a solution to it.

 

[IonelBurtan]

1.did you put rs.LockType=adLockOptimistic ?
2.you are using ADODB or RDS recordsets?
(also give me some more details of the lock mechanism you have now)

Good luck,s-)
Blessed is he who in the name of justice and good will, shepards the week through the valley of darknees...

http://home.domaindlx.com/ionelb

 

[Cyberseduct]

BurtanI,

Well for the locktype i am using adLockOptimistic and for the recordset It is ADODC.

below is a sample code i am using:

Dim Retry_Count%
Retry_Count% = 0
On Error GoTo Error_Loop

If Me.adoReceipt.Recordset.State = 1 Then
Me.adoReceipt.Recordset.Close
End If

If Me.adoCustomer.Recordset.State = 1 Then
Me.adoCustomer.Recordset.Close
End If

If Me.adoInvoice.Recordset.State = 1 Then
Me.adoInvoice.Recordset.Close
End If

Me.cmdSave.MousePointer = vbHourglass

With Me.adoReceipt.Recordset
.CursorType = adOpenDynamic
.LockType = adLockOptimistic

Dim ReceiptNo As Integer
If .State = 0 Then
.Open
End If
<&quot;Update Code Here&quot;>

Error_Loop:
Select Case Err
Case 3164
If Retry_Count% < 6 Then
LoopTimer = Time() + ((1 / 24 / 60 / 60) * 10)
While LoopTimer < Time
DoEvents
Wend
Retry_Count% = Retry_Count + 1
Resume
Else
MsgBox &quot; Table is locked by another user. Try again &quot;
endif
Case -2147467259

 

[IonelBurtan]

I have thought to some causes to your problem:

1) Try to change also the CursorLocation property for your recordset. try both adUseClient and adUseClient I don't remember which one is default.

I never used much ADO Data Control, instead I used ADODB.Recordset.
I had to tell you that I don't know why I didn't succeed in updating a SINGLE table in multiuser environment using the ADO locking mechanism. Sometimes worked, sometimes not, which was very annoying.

2)I invented myself a workaround like this:
1. add to each table that you will have to update a column named let's say REC_STATE with a default of &quot;&quot; (string)
2. when a user access the record put in this field the name of the user.
3. when a user saves the record put back &quot;&quot; into this field.

Notes:
- test this column before loading a record. if the record is used you give a message like &quot;Record is in use by username&quot; (now you know the user name, don't you).
- if you think about the way your aplication will work you will see that you have mych more control over the aplication, will little more effort in programming.

Hope this helps,s-)
Blessed is he who in the name of justice and good will, shepards the week through the valley of darknees...

http://home.domaindlx.com/ionelb