Record Locking

[Guest]

After years of creating stand-alone applications for my own use, I will be
launching a multi-user application for a small workgroup (15 people).
Pessimistic locking seems to be the best strategy for us. Do I need to set
the default record-locking strategy on each machine, or is it specific to the
database?

Thank you.
Sprinks

 

[Guest]

I'd use optimistic locking, if I were you. I rarely jave record locking
problems with more than 3 times the number of users you have.

Yes, you set locking on each machine.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

Arvin,

Thank you for your response. What do you do when you have a record locking
problem? Do you allow Access to display its messages, or do you employ
custom code? If the latter, can you provide any general guidelines for doing
so?

Thank you.

Sprinks

 

[Arvin Meyer [MVP]]

I usually will test to see if there is a lock for a few seconds, then use a
message if the lock doesn't clear. Here's a snippet of code:

Error_Handler: 'If someone is editing this record trap the error
If Err = 3158 Then
intRetry = intRetry + 1
If intRetry < 500 Then
Resume
Else 'Time out retries
MsgBox Error$, 48, "This record is locked"
Resume Exit_Here
End If
Else 'Handle other errors
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End If

500 tries takes about 3 or 4 seconds, which is usually enough.
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

Thanks again, Arvin.

Sprinks

I usually will test to see if there is a lock for a few seconds, then use a
message if the lock doesn't clear. Here's a snippet of code:

Error_Handler: 'If someone is editing this record trap the error
If Err = 3158 Then
intRetry = intRetry + 1
If intRetry < 500 Then
Resume
Else 'Time out retries
MsgBox Error$, 48, "This record is locked"
Resume Exit_Here
End If
Else 'Handle other errors
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End If

500 tries takes about 3 or 4 seconds, which is usually enough.
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com