'No locks' Advice

[dgunner71]

Hello, all.

New MS Access database rolled out 2 months ago - split into front end and back end. Each user has their own front end application. Average number of users is probably 2-4 simultaneous. Application is working great except...

I am getting odd Query Update error for lock violations. This is not happening in any consistent area of code but seems to be in general when a user is really 'bouncing around' and performing a lot of updates with a common Primary record. (i.e. updating 6 child records [not on a continuous form], all with the same parent record).

Before I ask my question - let me say that each time I open a record (form), I set a field [txtRecordOpenSt atus] to "Open - User Name" and then each time I close a record, I update the field to "Closed". I use this to prevent 2 users from accessing the same record. (my code throws an message - "You cannot access that record because Gunner has it open.")


So, with that said, my question is this - is it wise (dangerous?) to set the back end record locking to "no locks". It is currently set to "edited record".

I'm considering this as an option to alleviate this problem as I'm just not finding any rhyme or reason for the query errors. (image attached)

Thanks in advance for any insight and/or advice.

Gunner

[imgnothumb]http://bytes.com/attachment.php? attachmentid=75 67[/imgnothumb]

[zmbd]

So the user bounces around then the lock error occures.
This can be the result of a few different issues; however, it sounds more like a possible time-lag in that a record isn't being released before the next edit is happening.

What I would do, is try to recreate the issue with the multiple edits as you describe; however, at a snail pace to make sure the database has time to write to and release the record. If it occurs at the slower rate then you need to go back into the code/queries and determine where the conflict resides and attempt to resolve the issues there before you change the record-level locking.

[dgunner71]

Thanks, ZMBD.

I understand your logic in this approach. Based on my own experience with this issue, I'm thinking it has more to do with the time-lag (although I did find a problem that was with the 'doCmdRunSQL' statement in another form).

I'm planning on getting in there tonight to try to 'break' the system. I'll do it quickly to see if I do encounter the issue and then I can also try to do it slowly.

I'll update you on my findings. Record-locking (and 'jet' problems) are my most feared problems as I don't understand the underlying causes and don't have a high degree of comfort in fixing them. All in all, do you believe that these problems can be remedied in all circumstances (in a perfect world, assuming perfect design)?

Thanks again,

Gunner

[dgunner71]

ZMBD,

Just a thought - is there a way to programmically tell if a record is locked? I would prefer to give my own error message rather than the cold SQL error message. This would also be recorded (my system emails me when an error occurs) to help me troubleshoot potential causes.

I'm thinking something along the lines of:

dim intAttemptCount as Integer
    intAttemptCount = 0

tun_TryAgain:

If (intAttemptCount < 1) AND ({magic code to see if record is locked} = True) then
  Pause 2 Seconds 'Give Record some time to complete write process
  intAttemptCount = intAttemptCount + 1
  GoTo tun_TryAgain

elseif (intAttemptCount >= 1) AND ({magic code to see if record is locked} = True) then
  GoTo ErrErrorHandler

end if

  DoCmd.RunSQL "SQL Code here"

Thanks again!

Gunner

[dgunner71]

I think I'm (potentially) a bit closer to an answer on this issue. In all my forms, when a user opens a record, I set a field called [memRecordOpenSt atus] to "Open - User Name". Then, when I close the form I set the value to "Closed". (As noted above, I do this to prevent other users from opening a record that another user has opened - my system will give a message, "You cannot go to this record because User Name has it open already.")

I am setting these fields in the Form_OnLoad and Form_Unload events and, further, I am doing this through an SQL statement (see below).

    Dim lngID As Long
        lngID = Nz(Forms!frmSaleOrder.Form.AID, 0)
        
    DoCmd.RunSQL "UPDATE tblSalesOrders SET tblSalesOrders.memRecordOpenStatus ='Open - [" & gUser & "]' " & _
                    "WHERE ((([tblSalesOrders]![AID])=" & lngID & "));"

This seems to be working as intended in 99% of circumstances.

Gunner