Update Query Blocked by Error 3218 Record Locked - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Update Query Blocked by Error 3218 Record Locked

Michael Craytor

16 months ago

I have several tables that I view as continious subforms (like viewing Contacts for a given customer). The subforms have an "Edit" button and an "Add New" button that open a form for that table to add/edit records. When I close that form, it requeries the subform. My backend is online (WinHost/SharePoint), and due to major performance issues, I made local copies of the table structure, and I download the needed records from online when I load the database. When I add/edit, it works directly with the server tables, and then runs some VBA to sync the local tables again. But now I'm getting error 3218 when I edit a record for only some tables (no problem adding new records), the VBA hiccups on trying to run an UPDATE query to sync the local table. But only on 2 out of 5 tables I'm testing right now. 3 out of 5 work great. The forms are set up the same for all 5. All snapshot, no edits allowed, no record locks. I've tried adding timers/delays, clearing the recordsource of the subforms. UPDATE query works fine if I run it from Query Design, but not VBA. Anyone run into this? Unable to update a table that is the recordsource for an open subform?

Michael Craytor OP @Reply

15 months ago

Update: Best I can figure is that it is a SharePoint issue, that SharePoint is locking the SharePoint table (list) when a record is updated, but not if the table is fairly basic - so the sync works for some tables. The lock prevents the sync because the UPDATE query relies on an INNER JOIN with the SharePoint table. So even though I'm writing to a local table, I get a record lock error because it needs to reference the SharePoint table data. I haven't figured out when SharePoint finally lets go of the lock, but it seems that I just need to wait until all procedures and updates have completely finished, and then add a button to requery the subform. Still would be curious if anyone knows more about this apparent SharePoint quirk, other than that SharePoint is full of quirks (which I am painfully aware of :)).

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View