Record Locks
By Richard Rost
5 years ago
Record Locking: Prevent Multiple Users Editing
Learn how to enable record-level locking to prevent two users from modifying the same customer record at the same time in Microsoft Access.
Rosa from Toledo, Ohio (a Gold Member) asks: I have a small office with about 15 employees using my database. Everything works great, however once in a while two users attempt to edit the same customer record and one of them gets an error message saying they can't save their changes. This costs us a lot of time and productivity. Is there any way to prevent that?
Members
Members will learn how to create their own custom record locking which is more flexible, allows for clearer error messages, and will warn you before attempting to run action queries that may effect locked records.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Update!
- I have a new technique for programmatically testing for record locks without requiring a lock table. You'll find this in the extended cut for Optimistic v Pessimistic.
Links
 Keywords
microsoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, record locks, record locking, lock record, locked record, RecordLocks, lock files, record-locking, locking a record, record-level locking, write conflict, page-level locking, dirty records, no no symbol, locked symbol, this record has been changed by another user since you started editing it, drop changes, copy to clipboard, Access options, client settings, default record locking
Subscribe to Record Locks
Get notifications when this page is updated
Intro In this video, we will talk about how to enable and use record locking in Microsoft Access to prevent multiple users from editing the same data at the same time. I will show you how conflicting edits can occur in a shared Access database and how to change form settings to activate record-level locks. We will also cover the different lock options, the form-level nature of the setting, and how to change the default for new forms. Additional tips on related locking settings in Access are discussed as well.Transcript Welcome to another Tech Help video from AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, we are going to talk about record locking and how to prevent multiple users from editing the same data in your Microsoft Access database.
Today's question comes from Rosa in Toledo, Ohio, one of my Gold members. Rosa says, I have a small office with about 15 employees using my database. Everything works great. However, once in a while, two users attempt to edit the same customer record, and one of them gets an error message saying they cannot save their changes. This costs us a lot of time and productivity. Is there a way to prevent that?
Yes, Rosa, there is. We are going to activate something called record level locking. This means that when you edit a record, it locks it so no one else in the database can edit that specific record. Let me show you how to turn it on.
This video assumes you know how to properly split a database and that you are using a database on a network with multiple users. If you do not know what I am talking about, go watch my split database video. I will put a link in the description. I am assuming you have a back end and a front end database already set up.
Now, here is my folder for the record locking Tech Help video, which is what you are watching right now. I have a database, basically my Tech Help free template. You have seen this before in my videos. I have split it, so it has the same front end, with two copies of the front end and one back end file. The back end file contains my tables, and the two front end copies point to that back end. This setup allows you to simulate multiple users on a network by using two different copies of the same front end.
Here is front end one. I will slide this down and open front end two. Access now sees two different users in the same database. I will shrink one window and move it over. Now, let us open the customer form. Here I am on my record, Richard Rost. I will open the customer form in the second front end, and you can see it is the same data.
If I try to edit this record, notice the record is dirty. This is indicated by the pencil icon. If I move off the record, it saves the changes. If I open the form in the second front end, I will see those changes reflected. The problem arises when one user is editing a customer's record while another user opens the same record and makes a different change. Notice that the second user sees the old data. Access does not warn them that someone else is editing the record because record locking is not turned on by default. I will show you how to fix that in a moment.
Now, if I move off the record in the first window, saving those changes, and then move off the record in the second window, I get an error message. It says, The record has been changed by another user since you started editing it. This is not very user friendly. Access then gives me three options.
1. Save the record, overwriting the other user's changes.
2. Copy my changes to the clipboard so I can review them.
3. Drop my changes and accept the other user's edits.
If you are running a multi user database like Rosa, where 15 people are using the same database, this can be a big problem. Imagine if one of your reps is on the phone with a customer, adding notes, while another rep opens the same record and changes something else. That is where record locking comes in.
Record locking prevents this issue by ensuring that when one person edits a record, no one else can make changes to it at the same time. Instead of allowing conflicting changes, Access tells the second user to wait. This is preferable to letting them make edits and then informing them after the fact that their changes cannot be saved.
To enable record locking, close front end two. In fact, I will delete it and make a fresh copy. Now, in front end one, open your customer form in design view. Go to the properties window, click on the Data tab, and find Record Locks. The default setting is No Locks.
You have a couple of options here.
- All Records locks the entire table when a single record is edited. This is rarely preferable but can be useful in certain situations, like when working with a product list where no one should make changes while another user is editing.
- Edited Record locks only the record that is being edited. This is the best setting for most multi user databases.
Set it to Edited Record, close and save the changes, and then shut down the database. Now, copy front end one to create a fresh front end two.
Let us test it again. I will open both front ends. Here is front end one, and here is front end two. Now, I will open the customer form in both. I will start editing my record in front end one, and look at this. In front end two, I get a No symbol indicating that the record is locked. If I try to type, nothing happens. It is not very user friendly, but in the extended cut, I will show you how to make it clearer by displaying a Record Locked message or a pop up notification.
At least this prevents simultaneous editing, which solves the core problem. Now, if I move off the record in front end one, my changes are saved, and front end two can now edit it. The lock only applies while someone is actively making changes.
One thing to note. If a record is locked and you try to run an update query that affects the same table, you may get an error message. For example, let us say I have an update query that increases the family size field by one for all records. If a user is editing a record when I run the query, it will not update any records due to lock violations. There is not an easy solution to this with basic record locking, but in the extended cut, I will show you how to warn users before running the query.
Now, do you have to set record locking in each form individually. Yes, you do. Record locking is a form level setting, so if you have multiple forms that use the same records, you will need to enable it for each one.
You can also set record locking as the default for new forms. To do this, go to File, Options, Client Settings, and scroll down to the Advanced section. Look for Default Record Locking and change it to Edited Record. This ensures that any new forms you create will have record locking enabled by default.
There is also an option called Open Databases by Using Record Level Locking. This determines whether Access locks a single record or an entire memory page, which can include nearby records. For most small to medium sized databases, record level locking is best. If your database is large and you notice performance issues, you might want to experiment with page level locking.
That is how to set up record locking in Microsoft Access. If you are a Gold member, check out the extended cut, where I will show you how to make record locking more user friendly by displaying a message when a record is locked, create a custom lock table to track locked records, and prevent update queries from failing due to locked records.
To access extended cut videos, click the Join button below the video. Silver members and up get access to all extended cut Tech Help videos, live video and chat sessions, and more. Gold members can download the sample databases used in my videos and access my code vault, which contains tons of useful functions.
Platinum members get everything from the previous levels, plus access to my full beginner and some expert courses, covering Access, Word, Excel, Visual Basic, ASP, and more.
If you found this video helpful, please like, subscribe, and click the bell icon to receive notifications. YouTube no longer sends email notifications for new videos, so if you want email updates, join my mailing list.
If you have not tried my free Access Level One course, check it out. It is over three hours long and covers all the basics of building databases in Access. If you like it, Level Two is just one dollar or free for all channel members.
Want your question answered in a future video. Visit my Tech Help page and submit your question there. Thanks for watching.Quiz Q1. What is the main problem with multiple users trying to edit the same record in a Microsoft Access database without record locking enabled?
A. The database automatically merges both users' changes.
B. One user's changes may overwrite the other's without warning.
C. The form crashes immediately.
D. The data becomes permanently locked.
Q2. What does "record level locking" do in Microsoft Access?
A. Locks the whole database whenever one record is edited.
B. Prevents any form from opening if editing is in progress.
C. Locks only the specific record that is being edited so no one else can edit it at the same time.
D. Automatically saves all changes every second.
Q3. What is the default record lock setting in Access form properties?
A. Edited Record
B. All Records
C. No Locks
D. Page Level Locking
Q4. What happens if two users edit the same record at the same time without record locking enabled?
A. The first user's changes are lost.
B. Both users are prompted to merge their changes.
C. The second user receives no warning during editing and only sees an error when attempting to save.
D. Access automatically prevents both users from editing.
Q5. What are the three options Access gives if conflicting edits are detected?
A. Save, Undo, or Merge
B. Save the record, Copy changes to clipboard, or Drop changes
C. Save, Exit, or Retry
D. Ignore, Retry, or Override
Q6. What is recommended for most multi-user databases regarding record locking?
A. Use All Records locking
B. Do not use any record locking
C. Use Edited Record locking
D. Use Page Level Locking
Q7. If you want to enable record locking for all new forms created in Access, where should you change the setting?
A. Data tab of each form's property sheet
B. In the form design ribbon
C. File > Options > Client Settings > Advanced section
D. Right-click the table in navigation pane
Q8. Which option in record locking locks the entire table when editing any single record?
A. No Locks
B. Edited Record
C. Read Only
D. All Records
Q9. If a record is locked and you attempt to run an update query on the table, what will likely happen?
A. The query will succeed, but only on unlocked records
B. The query will update all records
C. The query may fail with a lock violation error
D. The query will ignore the locked records and proceed silently
Q10. Is record locking a database wide setting that applies to all forms automatically?
A. Yes, it is set once for the entire database
B. No, it must be set individually on each form
C. Only applies to queries, not forms
D. Only applies to reports
Q11. What visual indication appears in the second front end when a record is locked by another user?
A. A red exclamation mark
B. A No symbol (circle with a slash)
C. A warning banner
D. The record turns gray
Q12. The "Open Databases by Using Record Level Locking" setting influences what aspect of record locking?
A. Whether forms can be opened at all
B. How often backups are saved
C. Whether Access locks just one record or an entire memory page (affecting multiple records)
D. The maximum number of concurrent users
Q13. What should you do to simulate multiple users on a network when testing record locking?
A. Use two tables in the same database
B. Open two copies of the same front end connected to a shared back end
C. Split the database and work on the back end only
D. Use cloud storage to share the database
Answers: 1-B; 2-C; 3-C; 4-C; 5-B; 6-C; 7-C; 8-D; 9-C; 10-B; 11-B; 12-C; 13-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone focuses on record locking and ways to prevent multiple users from editing the same information at the same time in a Microsoft Access database.
Rosa, from a small office with about 15 employees, explained that although the database generally runs smoothly, sometimes two people attempt to update the same customer record simultaneously. This results in one of them being unable to save changes and seeing an error, which affects office productivity. Her question was whether there is a solution for this.
Absolutely, and the solution is to use record level locking in Access. When this feature is enabled, the record you are editing is locked so others cannot modify it at the same time. This requires that you've properly split your Access database and are already running front end and back end files on a shared network. If this setup is new to you, you might want to review my separate tutorial on splitting a database.
For demonstration, I use my basic Tech Help sample template. I've split the database, so there are two copies of the front end file, both connected to a single back end data file. This simulates multiple users working on the same data through separate front ends.
When two versions of the front end are open on the same record, say the customer form for a particular person, any changes in one front end show up after saving and moving off the record. However, when both users make changes to the same record at the same time, Access by default does not warn the second user that another person is modifying the data. The second user only finds out when they attempt to save, at which point Access displays an error explaining the record was changed by another user in the meantime. It then provides three options: save your changes over the other user's, copy your attempted changes to the clipboard for later review, or drop your edits and keep the other user's changes. This is not ideal for productivity, especially in a busy office where staff might be on the phone updating records concurrently.
Record locking addresses this issue. When enabled, Access locks a record when someone starts editing it so no one else can change that record until it has been saved. This prevents conflicting edits before they happen, requiring the second user to wait until the record is available.
To turn on record locking, you must set it up in each form where you want to apply it. Open the relevant form in design view and locate the Data tab in the properties window. Under Record Locks, you'll find several options:
- No Locks, the default, allows multiple edits at once but risks conflicts.
- All Records locks the whole table when any record is being edited; usually not helpful for most day-to-day work unless, for instance, only one person should be changing any product information at a time.
- Edited Record, which is the preferred setting for multi-user databases, locks just the record that is currently being changed.
Switch to Edited Record, save and close the form, then make sure that fresh copies of the front end are used if other users are accessing the database. After making this change, when two users attempt to edit the same record, the second user will see that the record is locked and will be unable to make changes until the first user has finished and saved. While Access's own warning isn't very visually obvious, at least it prevents the core problem of clashing edits. In the extended cut for today's lesson, I'll show you more user-friendly options, like displaying a pop-up or notification box when a record is locked.
Another thing to keep in mind: if a record is locked, and you try to run an update query on the table containing that record, you may receive an error for those records that are currently locked. For example, if someone is editing a record while you try to run a query that updates several records, lock violations can prevent the updates from happening. There is no simple solution for this behavior with basic record locking, but again, in the extended cut, I will walk you through ways to warn users before running such queries.
Record locking must be enabled separately for each form. As a convenience, you can set Edited Record as the default for all new forms by going to File, Options, Client Settings, and scrolling down to the Advanced section. Set Default Record Locking to Edited Record so future forms have this setting automatically.
Access also has a setting called Open Databases by Using Record Level Locking, which determines whether an individual record or a group of records (a page) gets locked. Record level locking is usually adequate for small to medium sized databases. For very large databases where performance issues arise, it may be worth testing page-level locking.
That covers the basics of setting up record locking in Microsoft Access. For Gold members, today's extended cut explores making record locking more user-friendly by displaying messages when a record is locked, setting up a custom lock table to monitor who is working on what, and developing warnings for users before running update queries that might fail.
If you want access to extended cut videos and other benefits such as download access to sample databases and my code vault, consider becoming a Silver or Gold member. Platinum members receive all of these benefits and access to my complete beginner and some expert courses in Access, Word, Excel, Visual Basic, and more.
If you are new to Access or want a refresher, my free Access Level One course is available and covers all the basic foundations for building databases. Kick off with that, and if you want to continue, Level Two is just one dollar or free for members.
If you have questions you would like featured in a future video, you can send them on my Tech Help page.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Preventing multiple users from editing the same record
Demonstrating the problem with default record locking
Explaining dirty records and the pencil icon
Understanding Access record save conflicts and options
Activating record level locking on forms
Difference between All Records and Edited Record lock options
Testing record locking with multiple front ends
Impact of record locking on update queries
Setting record locking on each form individually
Setting default record locking for new forms in Access Options
Explaining record level vs page level locking settings |
