Welcome to another Tech Help video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, we're going to talk about record locking. We're going to prevent multiple users from editing the same data in your Microsoft Access databases.
Today's question comes from Rosa from 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 can't save their changes.
This costs us a lot of time and productivity. Is there a way to prevent that? Well, yes, of course, Rosa, there is.
We're going to activate something called record level locking. This is where 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. Now this video... assumes you know how to properly split a database and that you're using a database on a network with multiple users if you don't know what I'm talking about go watch my split database video.
It's right there. I'll put a link down below in the link section I'm assuming you've got a back-end and a front-end database already set up, okay So here's my folder for the record-locking tech help video, which is what you're watching right now, right? And I've got a database.
This is basically my tech help free template Alright, you've seen this before in my videos, and I basically split it. Alright, it's the same frontend, I've got two copies of the frontend, and I've got one backend file. The backend file has my tables in it, okay, just like you normally would do.
Okay, and then frontend1 and frontend2 both point to that backend. So you can basically simulate multiple users on a network by just using two different copies of the same frontend. Okay, it works the same way.
So here's front end one. I'm going to just slide this down like this. Watch this.
We'll do that. And here's front end two. Let's open up front end two now, and Access will think we've got two different users in the same database.
Okay, shrink that, and we'll move this guy over here. Okay, user one, user two. Make them a little bit more equal there. Okay, there we go.
Alright, so let's open up the customer form. Here I am on my record, Richard Rost. Okay, open up the customer form over here, and you can see it's the same data.
Okay, now, if I try to edit this one, notice the record is dirty. That's what the pencil means. Okay, if I move off the record, the record then gets saved.
Now, if I open it up over here, you'll see there's the changes. Okay, the problem comes into play if this guy is editing this customer's record. and then another user comes into the same record notice it's the old data right and tries to make a different change now access lets you doesn't tell you that hey someone else is editing this record right now because record locking by default is not turned on so i'm going to show you how to do in a second all right now i move off the record over here which saves that one and as soon as i move off the record over here i get an error message see right conflict the record has been changed by another user since you started editing it Well, it would have been nice if Axis would have told me, right? So you can either save the record overwriting his changes. What if he made some other change that's important?
Okay. Copy the clipboard, which copies your changes to the clipboard so you can look at them or drop changes, which means forget what I just did, right? I don't matter.
I'll go drop changes and it brings in what the other person changed it to. Okay. So that's not very user-friendly. That's not a good solution if you're in a database where you've got, like Rosa said a minute ago, she's got 15 people using the same database, and we chatted a little bit in email.
It's not a very huge database. They only have a few hundred customers. But if one rep is on the phone with a customer and puts in some notes, and then another rep opens it up with the same customer and changes something, well, you've got problems, as you can see.
So how do we fix this problem? Well, we're going to turn on record locking. Now, record locking is where Access basically says, hold the press.
Nobody can change this customer record because someone else is editing it. You're going to have to wait. All right, and that's preferable to letting you make your changes and then saying, well, too bad. Okay, so I'm going to close down front end two. In fact, I'm going to delete front end two because we're going to make a copy of it now.
So goodbye, front end two. See you. All right, now I'm in front end one over here. Okay, front end one.
Now go into your customer form, right click. design view open up the properties data record locks by default the default is no locks now you got a couple options there's all records or edited record all records is usually not preferable all records means as soon as someone starts editing any customer all of the customers get locked now you might want to do this in a situation where you've got a certain list like let's say a product list or something and you don't want anybody messing with any products if you're editing one of them there are some situations where i have used all records before but generally you want edited record the edited record means when i edit richard ross nobody else can edit richard ross okay so put it on edited record close it save changes now i'm going to shut this database down and i'm going to copy it so we have a fresh copy of front end 2. all right and front end 2. so there's my second user all right let's open them back up again there's front end one come on and it moved you okay get back over there where i had you shrink you up a little bit all right and front end two open it up second user comes in the network okay and let me fix you slide you over there resize a little bit and that's good enough okay all right ready here we go same thing customer form richard rost I'm going to start changing you. Put some stuff in here.
Customer form over here. Ah, look at that. See that?
That little circle with the line through it? The no-no symbol, I call it? No-no means it's locked.
If I come over here and try typing, see? Now, it's not very user-friendly. And in the extended cut, I'm going to show you how to make it so that they know it's locked.
It'll say locked up here. Or you can post up a... put up a message box or something but at least it prevents this record from being edited right i'm typing right now and nothing's happening you can hear the beeping okay but if i close this come over here move off of that record and now i come in here look at the changes are in there and i can now edit this record myself see now i've got it accessed and if this person comes back over there all right now the changes have been made but sometimes this doesn't update a hundred percent of time but it's still locked see as soon as i start typing okay sometimes that dirty this little symbol doesn't appear but trust me it's locked usually if you close the form and come back in you'll notice it's definitely there okay close this close this open it back up again changes are in there now do you have to go and set that property in each of your forms yes you do all right this is something that happens at the form level so if you've got a different form okay that utilizes the same records like for example if i look in my main menu here i have a customer with contacts form all right this guy also uses the customer record but i have to make sure i come in here now all right and turn on record locks set the no locks this form will not all right edited record that's important all right so you'll have to change all of your existing forms because when access ships the default setting is no locks now you can change the default so that any new forms you create have record locking turned on let me show you where that is file options okay we're in the access options come down to client settings scroll down And where are they at?
Under Advanced here, you'll see Default Record Locking. Now, mine is set to the edited record because this is the machine that I usually develop on and teach classes on. But the default when you get access, and yes, I verified this, is no locks. By default, access does no locking.
I've already changed mine to edited record. That's usually where you want yours to be if you have a multi-user database. It's more efficient if you're the only user. to have it on no locks that's why they distribute it that way because if you're a single developer you're building a database for yourself there's no need for record locking if you're the only user but as soon as you get get advanced enough i was going to say smart enough that's not the case as soon as your database gets big enough to where you're sharing it with multiple users and you know about record locking and splitting your database then you come in here and say edited record now there's also an option here that says open databases by using record level locking what does that mean Well, once you start dealing with record locking, there's two kinds.
There's record level, and there's page level. Okay? Record means lock just that single record. A page is basically, it's a memory thing inside the computer, but a page basically, depending on the size of your table, can be the record and maybe a few records around it. And the reason why it does that is for efficiency.
Okay, if you've got a really big database with tens of thousands of records, it's more efficient for access to lock a page. It's quicker, okay, than it is to lock a single record. I'm not going to get into the details of which and why and all that stuff. But basically, if you've got a huge database, you should be using SQL Server.
First of all, if you've got a huge database. But you might find your database performs better if you turn this off. But if you've got Joe Smith locked, and he's customer 14, and Sue Jones is customer 15, she might be locked also. If your database is that big, chances are you're not going to run into those kind of collisions. But for small, normal, average-sized databases with a dozen users, I would just leave this on.
Use record-level locking. It'll lock the record that you're on. If you find your database starts running small, slow, turn that off. As far as slow databases go, you can also play with these settings here, which this is a topic for a totally different video.
But hit OK. Now any new, you might get, yeah, you got to close the database or reopen it. But now any new forms that you create, all right, create, form design.
data now it's edited record is the default so if you're building new forms from this point on that setting will be set for you so i hope that shows you what you need uh you're a gold member so watch the extended cut in the extended cut i'm going to show you how to do a little more how to give the the the user um like put it right up here in the title bar that the record's locked okay uh the little no-no symbol is helpful but sometimes people miss it and i have forms where i hide this the record selector I hide it completely, so they've got no way to know what's wrong. All right, so in the extended cut, we'll make this a little more user-friendly. Almost forgot to mention, there is one problem with record locking like this, and that's if you have any records locked, but yet you have, let's say, an update query that affects any record in that table, it may cause an error message.
Let me show you an example. Let's say, hypothetically, you've got a query. an update query if you don't know what update queries are by the way go watch my video on update queries i'll put a link down below but an update query can be used to change values in the records in your table now let's just pick something let's say we've got uh family size okay let's say i've got an update query that runs that increases family size by one every time i run it yeah it's a it's a silly example but it could be anything it could be updating order dates it could be updating shipping times whatever okay it doesn't matter this is just what i'm picking for class so create And then query design, I'm going to bring in my customer table, and I'm going to say family size, all right?
And we're going to set this to an update query, update to family size plus one. It's a silly example, I know, right? But let's say update family, Q. Okay, now what's my family size right now?
Two. If I run my query, update family. Alright, if I check it again, now it's 3. That's all that does is it updates the family size. Get it?
Got it? Okay. Now, let's say frontend2 is open. Okay, here's frontend2.
And in frontend2, someone is editing me. Okay. Yeah, if I open this up, I can see that that user is being edited.
But now let's try running my update query. And look it it's kind of stuck. It's sitting there. Nothing's happening.
See I mean we get the not responding right now takes a second Come on. There we go See can't update all the records and it says six records due to lock violations It won't update anybody if any record is locked in that table, then this query fails Okay, I just want to make you aware of this There's really no easy solution with basic record locking Okay. However, in the extended cut, I will show you with my record locking that I've developed, how you can at least give the user a warning message before you try running that update query to say, hey, there's a customer locked.
You can't do this right now. Okay. Which really, there's no way to do that here easily.
Okay. So that'll be covered in the extended cut as well. All right. Here's the extended cut database. All right.
What we do is we create our own lock table. It's basically got an ID in it where you can put in here any customers that are locked. That way we can read it and do stuff with it. Okay, if I open up my customer form, here's user one, here's user two. Okay, if I start editing this guy over here, notice it's locked.
All right, if I move away from this one and come back to it, look, customer one's locked and I can't change it. I'm trying to type and nothing happens. Okay, if I go over here and try to edit Jim Kirk.
Now this one's locked. And if I take a peek in the table, here's the lock table. See?
They're both locked, one and two. All right? If I leave this one and come over to James, now this one shows lock because he's got it locked over here. See? All right?
And you can pop up any kind of message box you want. You can change the background color, all kinds of stuff. All right? But now you control the record locking. You control what can happen.
Right? Also, this hello world thing. Okay? What I did was... i just made that query so that it checks the locking table if any customers are locked it won't run the query so right now i just clicked it it says query running done it's it's doing its thing but if there's a locked customer okay this guy's got a locked customer now if i come over here and try to run this query see cannot run customers locked because i control the record locking i can do what i want all right and i cover this in the extended cut all right so that's the extended cut from members we'll create our own record locking table we'll make a custom set of events so we lock and unlock the record at the appropriate times we'll change the forms caption but you could easily modify my code to do whatever you want change the background color of the form all kinds of stuff and then we'll also have a way of checking to see if any records are locked before we run a query that might manipulate all the records in the table that can come in handy sometimes so that's all covered in the extended cut for members still remembers and up Get access to all of my Extended Cut videos.
Gold members can download my Tech Help databases. How do you become a member? Click the Join button below the video. After you click the Join button, you'll see a list of all the different types of membership levels that are available.
Silver members and up will get access to all of the Extended Cut Tech Help videos, live video and chat sessions, and more. Gold members get access to a download folder containing all the sample databases that I build in my Tech Help videos, plus my... Code Vault where I keep tons of different functions that I use.
Platinum members get all the previous perks plus access to my full beginner courses and some of my expert courses. These are the full-length courses found on my website and not just for access I also teach Word, Excel, Visual Basic, ASP and lots more. But don't worry these free tech help videos are gonna keep coming as long as you keep watching them I'll keep making more. If you liked this video please give me a thumbs up and feel free to post any comments that you have I do read them all.
Make sure you subscribe to my channel, which is completely free, and click the bell icon and select all to receive notifications when new videos are posted. Click on the show more link below the video to find additional resources and links. You'll see a list of other videos, additional information related to the current topic, free lessons, and lots more. YouTube no longer sends out email notifications when new videos are posted, so if you'd like to get an email every time I post a new video, click on the link to join my mailing list. Now if you have not yet tried my free Access Level 1 course, check it out now.
It covers all the basics of building databases with Access. It's over 3 hours long. You can find it on my website or on my YouTube channel.
And if you like Level 1, Level 2 is just $1. And it's also free for all members of my YouTube channel at any level. Want to have your question answered in a video just like this one?
Visit my Tech Help page and you can send me your question there. Click here to watch my free Access Beginner Level 1 course, more of my Tech Help videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com.