title: Instantly "locking" a record in multi-user Access environment
body: <p>So, record-locking in Access is pretty awful. I can't use the built-in record locking because it locks a "page" of records instead of just the individual records (I've tried changing the settings for using record-level locking, but it's still locking a page instead of just one record), but even if I could get that working, it wouldn't solve my issue because the record doesn't lock until the user starts to make changes in the form.</p>
  
  <p>The issue is, when two people open the same record, they can start making changes and both save (thus overwriting the earlier change). To make matters worse, there are listboxes on the form that link to other tables (keyed on an ID) and the changes they make to those tables are then overwritten by any change that comes after if they both opened the same record.</p>
  
  <p>Long story short, I need to make sure it's impossible for two people to even <em>open</em> the same record at the same time (regardless of whether or not they've made any edits to it yet).</p>
  
  <p>To do this, I added a field to the table which indicates if a record has been locked by a user. When they open a form, it sets their name in the field and other users who try to open that record get a notification that it's already locked. The problem is, this "lock" isn't instantaneous. It takes a few seconds for other users to "detect" that the record is locked, so if two people try to open the same record at roughly the same time, it will allow them both to open it. I've applied a transaction to the UPDATE statement that sets the lock, but it still leaves a short window wherein the lock doesn't "take" and two people can open the same record.</p>
  
  <p>So, is there a way to make an UPDATE instantaneous (so all other users immediately see its results), or better yet, a robust and comprehensive way to lock records in an Access multi-user environment?</p>