Locking Rows

  • Thread starter Thread starter Max
  • Start date Start date
M

Max

I am trying to track down why the below code runs slowly(not my code :o)).
It works ok with one user, but with more it is very slow (30-60secs):
This is updating the same row every time. So forgetting the error handling,
as this module does, is it because this row is not released? If so can i set
it so that it is released? This is A97, thanx

Dim strSQL As String
If Len(Trim(strPO)) > 0 Then
strSQL = "UPDATE [tblCurrentUsers] SET [PurchaseOrderNo] = '" &
strPO & "' WHERE [UserName]='" & CurrentUser() & "'"
On Error Resume Next
Debug.Print "executed: " & strSQL
CurrentDb.Execute (strSQL)
On Error GoTo 0
End If
 
Is this code using Linked Tables or Actual Tables within the same DB file as
what the code is in?

If done via Linked Tables, does multiple users access the same DB file at
the same time?

If the answer to the first question is "Actual Tables" or the answer to the
second question is "Yes", then the best practice for a multi-user
environment of a FE/BE DB system is not being utilized, thus could be why
you are running into this issue.
 
This is using linked tables, with users having their own FE database going
to a BE database. Mutiple users, access the same record on the BE, but
rarely concurrently. I have put bound a BE table to the FE, so the
connection is kept, but this particular part is stubbonly slow. Is it
possible that the row is being kept locked?

any thoughts?


Ronald Dodge said:
Is this code using Linked Tables or Actual Tables within the same DB file as
what the code is in?

If done via Linked Tables, does multiple users access the same DB file at
the same time?

If the answer to the first question is "Actual Tables" or the answer to the
second question is "Yes", then the best practice for a multi-user
environment of a FE/BE DB system is not being utilized, thus could be why
you are running into this issue.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

Max said:
I am trying to track down why the below code runs slowly(not my code :o)).
It works ok with one user, but with more it is very slow (30-60secs):
This is updating the same row every time. So forgetting the error handling,
as this module does, is it because this row is not released? If so can i set
it so that it is released? This is A97, thanx

Dim strSQL As String
If Len(Trim(strPO)) > 0 Then
strSQL = "UPDATE [tblCurrentUsers] SET [PurchaseOrderNo] = '" &
strPO & "' WHERE [UserName]='" & CurrentUser() & "'"
On Error Resume Next
Debug.Print "executed: " & strSQL
CurrentDb.Execute (strSQL)
On Error GoTo 0
End If
Click to expand...
Click to expand...
 
Hi Max,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.

From the information you provided, you execute update on a front talbe
linked with a backend table, and you found that when more users update the
same table, it became slow. You wonder if the record is still locked, which
blocking the other user, right? I suppose that this change happened only
after only one factor change, that is, more user carried out the same
update. If I misunderstood, please feel free to let me know.

First, please make sure that when one user connect the table and execute
the update, it works fine. Then take the following steps:

Open the databases you use, from the menu, choose 'Tools', choose
'options'. In the 'Advanced' tab,
1) Change the 'Default Open Mode' to 'Shared';
2) Change the 'Default Record Locking' to 'no locks' since in you update is
according to the currentuser();
3) Reduce the 'refresh intervals';

After this steps, execute the update from one user, then one more user to
connect and execute the update to check if any change.

If there are other factors besides the user number which would affect the
update, for example, network connection.


Best regards

Baisong Wei
Microsoft Online Support
 
thanx Baisong,

You are correct in what you say. i'll try the changes. The network is slow,
(10MB Hubs etc) but this transaction is especially slow.

salud
 
Back
Top