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.