[AccessD] Recording Locking

[Jim Dettman]

I should have added to turn off delete confirmations, or you can do:

Dim db As DOA.Database

Set db = CurrentDb()

With db
  .Execute "DELETE * FROM xyz WHERE ????"
  If . RecordsAffected = 0 Then
     MsgBox "Error"
  Else
      Msgbox . RecordsAffected & " records deleted."
  End If
End With

Set db = nothing

Jim.

-----Original Message-----
From: AccessD On Behalf Of Jim Dettman via AccessD
Sent: Wednesday, January 10, 2024 8:57 AM
To: 'Access Developers discussion and problem solving'
<accessd at databaseadvisors.com>
Cc: Jim Dettman <jimdettman at verizon.net>
Subject: Re: [AccessD] Recording Locking

Think you've summed it up nicely.

That's the way it works.

Jim.

-----Original Message-----
From: AccessD On Behalf Of Ryan W
Sent: Wednesday, January 10, 2024 8:43 AM
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Subject: [AccessD] Recording Locking

Hey all,

I had an interesting situation I don't think I've encountered before:

I had a user come to me the other day and said they couldn't run one of the
calculation command buttons. I ran it and got a query timeout.

I checked for blocking SPIDs and found the one blocking this.  I went back
to that workstation and the user had highlighted rows from a datasheet and
pressed the delete key but left the deletion prompt up and walked away.

This left a transaction open that was blocking other users from
modifying/inserting/deleting rows in the same tables, even if it was
unrelated data.  I'm guessing the primary key column(s) were locked up in
all of this.  Once I hit OK or Cancel on that dialog prompt, the system
returned to normal.

The only 'fix' I can think to come up with would be to make a delete button
that uses a MsgBox and then runs the delete code on the selected rows,
since that would only run AFTER the MsgBox OK button is pressed...  or just
tell my users "don't walk away from confirmation dialogs".

Anyone got any words of wisdom on this?
-- 
AccessD mailing list
AccessD at databaseadvisors.com
https://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
https://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com