Logged On
By Richard Rost
4 hours ago
How to Tell if Anyone is Using Your Access Database In this lesson, we will discuss how to tell whether a shared Access back end is probably in use by checking for its LACCDB or LDB lock file. I will show you how lock files work, why stale lock files can be misleading after a crash or lost connection, and how to build a simple VBA function that checks whether the back-end lock file exists before you perform maintenance or design changes. Nathan from Boise, Idaho (a Gold Member) asks: I have a split database that several people use during the day. Every so often I need to compact the shared data file, restore a backup, or make a table change, but I never know whether someone is still working in it. Sometimes Access says the file is already in use, but nobody admits to having it open, and I do not want to interrupt someone entering orders. Is there a quick way to tell whether the shared backend is actually being used before I do maintenance? MembersIn the extended cut, we will upgrade the function to loop through all linked tables, identify their back-end files, and check each lock file while skipping duplicate back-end paths. 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!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, Access lock file, LACCDB file, shared backend maintenance, split database, stale lock file, check database in use, VBA Dir function, exclusive database access, LDB file, linked tables, compact and repair, lock file workstation names
Subscribe to Logged On
Get notifications when this page is updated
Intro In this lesson, we will discuss how to tell whether a shared Access back end is probably in use by checking for its LACCDB or LDB lock file. I will show you how lock files work, why stale lock files can be misleading after a crash or lost connection, and how to build a simple VBA function that checks whether the back-end lock file exists before you perform maintenance or design changes.Transcript Have you ever needed to compact your back end or make a design change or restore a backup, replace a file, something like that, and Access tells you somebody else might be using it?
The problem now is who?
Is someone actually in the database, or did they leave Access open three days ago and go on vacation?
You don't want to start making changes to a shared back end while somebody's entering invoices.
That's a good way to turn a normal Tuesday into an interesting Tuesday.
Welcome to another TechHelp video brought to you by Access Learning Zone.
I'm your instructor, Richard Rost.
Today we're going to take a look at a quick, practical way to tell whether your shared Access back end is probably in use or not before you perform maintenance on it or do something like make design changes.
I'll show you what to look for, what it can tell you, and one important situation where it can be a little misleading.
We'll also build a simple VBA check that you can use in your own database to help keep you from working on a file while somebody else may still be connected.
Today's question comes from Nathan in Boise, Idaho, one of my Gold members.
Nathan says, "I've got a split database that several people use during the day. Every so often I need to compact the shared data file, restore a backup, or make a table change, but I never know whether someone is still working on it. Sometimes Access says the file is already in use, but nobody admits to having it open, and I don't want to interrupt someone entering orders. Is there a quick way to tell whether the shared back end is actually being used before I do maintenance?"
Alrighty, let's talk about it.
Before you start messing with a shared database file, it's a very good idea to find out whether anybody is actually still in it.
Now, the quick answer is, look for Access's lock file.
Whenever Access opens a database for normal multi-user use, it generally creates a tiny companion file in the same folder.
So if your database is an ACCDB file or an ACCDE file, the companion file ends in LACCDB.
And if you're still living in 2002, if you're using an older MDB database, the companion file is LDB.
You can think of these files as an occupied sign on a restroom door.
It doesn't necessarily mean that someone's in the restroom. They just put the occupied sign on there.
They could have left a while ago and forgot to take the sign down.
We'll talk about that in a little bit.
But the existence of that file could be just a quick warning that somebody may be using that database file.
So what is this thing exactly?
The lock file is a small working file created by the Access database engine.
Its job is to help Access coordinate multiple people using the same database.
For example, if one person is editing a customer record, Access needs a way to manage record locks and keep everybody from stepping all over each other.
So the lock file is part of that behind-the-scenes plumbing.
If the database is named CustomerData.accdb, the lock file is normally CustomerData.laccdb.
Now, normally, when the last user closes the database properly, Access deletes the lock file automatically.
So it's not something you have to manage yourself.
Don't rename it, edit it, or move it just because you see it there.
It's not clutter. It's Access doing its job.
Unless you're certain everybody's out of the database and you've still got a lock file, we'll talk about that in a second.
So the simplest method to check to see if people are in your database is just find the folder where the database file is and see if that lock file is there.
Now, this is not the same as the lock file for your front end database.
You should have a front end database on each user's computer. That file shouldn't live on your network.
And that file doesn't really matter because that's just for that database.
The back end, the shared database on your server, that's the file we're looking for.
Also, make sure if this is on a network drive, hit F5, the refresh button, because sometimes on a network, you might not be seeing everything exactly as it is right now.
Sometimes you have to manually refresh that folder.
I know I have to once in a while on my network drives.
Now, if that file is not there, that's almost certain that no one is using the database and it's safe to do whatever work you have to do.
The lock file is not there. You can be almost certain nobody is actively using that file.
So you can go ahead and compact and do whatever you've got to do, make your design changes, restore a backup, whatever.
I would recommend opening it exclusively to make sure that no one else gets in the database.
That's a whole different video.
But you should be good to go.
Now, if that lock file does exist, someone may have the database open.
Maybe they're entering an order, or they've got a form open, or whatever.
Or they might have gone to lunch and just left Access running and not actually doing anything.
So in that case, you should treat the back end as the occupied bathroom stall and avoid doing any work on it right now.
But this is only a probably because, like I said, they could have gone to lunch.
Or worse yet, last week, Monday, Joe could have been in the database and Access crashed on him.
That's the usual situation with that lock file being left when no one's actually in the database.
Usually it's a crash.
Maybe Access froze, maybe Windows crashed, maybe the computer lost power, maybe somebody's Wi-Fi did the classic disappearing act, or maybe a network connection dropped before Access had a chance to clean up properly and delete that file.
In those situations, Access may leave that lock file behind.
That's what we call a stale lock file.
And a stale lock file creates a false positive.
You see the file and think somebody's still in the database, but they're not.
This is why I never say 100% guaranteed that the lock file means that there's a live user that's in the database right now.
The safe procedure at this point is to confirm that everybody is out of the application.
And this might be one of those cases where you've got to run around to everyone's workstations and so on.
"Are you logged in?" "No." "Are you logged in?" "No."
There's nothing you can do about that.
Now, usually, once you've confirmed that no one's using the database, you can delete the stale lock file.
And even then, make sure you're removing the lock file and not the ACCDB database.
I've accidentally deleted the database myself before when I thought I was deleting the lock file.
So don't do that.
And also, what I've done in the past, and you didn't hear this from me, folks, so I'll deny it, what you could do if you think there might be one or two people still using the database, but you're not sure, maybe it's late at night and you don't feel like running around all the workstations, most people are gone, you could try deleting the lock file and just wait a minute or two.
If it comes back, that means someone is definitely in the database.
But if you wait like five minutes and that lock file stays gone, that's probably a stale lock file.
But I don't recommend doing this during the middle of the day when there's 30 people using your database.
This is like one of those, "It's 3 a.m. You're trying to see if maybe it's a stale lock file."
So that's just one of my tricks.
Now, here's one little caveat.
Here's one little subtle point that catches people occasionally.
In a split database, your front end can open to an unbound main menu.
Mine mostly do.
The TechHelp Free Template, which we're going to see in a minute, my main menu is unbound.
Unbound just means the form is not directly displaying data from a table or query.
And if that startup form hasn't touched any of the linked back-end tables yet, Access may not immediately need to open the back end.
So somebody could have their front-end application open, but the back-end lock might not appear until they actually open a customer form or run a query, process an order, or otherwise use a linked table.
So for all intents and purposes, this means the lock file tells you whether somebody is using that back end, which is what matters more before you do any maintenance or edit the back end.
So today's walkthrough, let's go ahead and build a little simple test function to see if your back-end file is currently locked.
We're going to check to see if that LACCDB file exists.
And if it does, then we'll say, "Okay, it might be locked."
It's not 100%, but chances are it is because that file exists.
And if it's not there, then chances are it's safe to go ahead and do your maintenance.
This is a little button you could put around your main menu, because your database, as long as you haven't opened a table yet, your main menu shouldn't touch that lock file yet or touch that back end and create the lock file.
So at least this is one little button that you can click on and say, "Is it safe to use the database?"
Let's see how we build that.
Before we get started, today this is a developer-level lesson.
So if you've never done any VBA programming before and you want to learn how, go watch this video.
It teaches you everything.
In about 20 minutes, to get started, we're going to create our own function today.
So if you've never made a function before that returns a value, go watch this video.
We're going to use some variables today.
You should know how to work with a string function, specifically Left.
We're going to use the Left function today.
And you should be familiar with the Dir function.
Dir.
If you haven't watched any of these videos or if you need to learn how to do this stuff, I'll put links down below.
They're on my website. They're on my YouTube channel.
These videos are all free.
Go watch them and then come on back.
Oh, and I almost forgot.
I assumed everybody knows what a split database is if you're watching this video.
If not, go watch this video.
So here I am in my TechHelp Free Template.
This is a free database you can grab off my website if you'd like to.
And I've split it.
And yes, I'm using dark mode because it's late at night when I'm recording.
My eyeballs like dark mode at night.
What's dark mode? Go watch this.
So anyways, I've split this database.
So here's the front end.
This is the front-end file here.
The back-end tables, they're in a file called BackEnd.
Here's the folder.
Let me show you where the folder goes.
Here's the folder.
I just put it on my desktop in a folder called Lockdown.
Normally this will be your server.
And you can see here, the front end has an LACCDB file.
The back end does not because I haven't opened any tables yet.
Watch this, though.
If I move this over here, I go back in the database.
I'm going to resize this so you can see everything working at the same time.
So if I open up my customer form, notice right there that back-end file appeared because I'm in the customer form.
If I close it, it goes away.
And that's normally how it works.
And if you've got three, four, five people in your back end, that file will be there.
And then, as long as they all exit out, then the file gets deleted.
But if Joe from accounting, his system locks up, and he says, "Screw it," and he turns the computer off and goes home, that file might still be open.
And the LACCDB file might still exist here.
So like I said, this isn't 100%.
It's usually good enough, though.
So all we're going to do is, we know what the name of this file is, this back-end file.
We're just going to make a little button here that says, "Hey, is anybody in the back end?"
It's simple.
That way you could just go about your business.
So I'm going to go down here.
I've got a global module.
This is where you put stuff that you want to be able to be used anywhere in the database.
I'm just going to come down to the bottom here.
We're going to make a public function.
Function means it returns a value.
We're going to say:
Public Function BackEndLockFileExists(PathFileName As String) As Boolean
This is going to return a Boolean, a true-false value.
So I like to put stuff like this in my own local, easy-to-use variables.
Dim s As String
I can't type today.
It's late, folks. That's why.
So we're going to start off by saying:
s = PathFileName
Just put it in there.
Now, what's going to happen is we get the path filename.
We're going to send it this.
We're going to send it C, whatever, the full path to this guy, .accdb.
So we're going to just chop off the .accdb and add .laccdb to it.
And, of course, if you're still using MDB files, you have to write a separate one for MDB files, or look to see what the right X number of characters is.
So I'm going to say I'm just going to assume it's an ACCDB file.
So:
s = Left(s, Len(s) - 5)
So however long that string is, minus five characters.
So this removes the ACCDB extension.
And then we're going to add onto it:
s = s & ".laccdb"
Now we've got the filename we're looking for.
So we're going to say:
If Dir(s) = "" Then
BackEndLockFileExists = False
Else
BackEndLockFileExists = True
End If
Dir returns an empty string if that file does not exist.
Yes, I know there's more compact ways of writing this.
You can say BackEndLockFileExists equals and then put this in front.
I get it.
This is more readable, and I like readable code.
It doesn't matter to the compiler.
I like readable code.
So when I look at this two years from now, I can very easily, in my brain, understand what's going on here.
I know it's not the most compact code.
Debug compile once in a while.
Everything compiles.
Now we can call this function from our main menu to see if this file exists.
So let's close this.
I'm going to go into design mode here.
I'll just hijack the Hello World button.
That's why I have it on here.
Check Lock File.
Right-click, Build Event.
And you could message box, or you could use my status function.
What's my status function?
Instead of message boxing, I like to just put messages on this box on the main menu.
That's my status box.
Go watch this.
I get YouTube comments all the time: "How do I find the status function?"
So we're going to use:
Status BackEndLockFileExists
as the function.
And we're going to send it the path filename of our back-end file.
So this guy, right-click, Copy as Path.
That will copy its entire path and filename to your clipboard.
And then just stick that in there.
And there you go.
That'll show whether it exists or not.
Save it.
Yes.
Close it.
And then we can close that and reopen this.
And then click the button.
And there's a False.
I mean, it doesn't exist.
We can make that a little more friendly.
Let's do this:
Dim BEExists As Boolean
BEExists = that.
If BEExists Then
Status "Lock file does exist"
Else
Status "Lock file not found"
End If
How's that sound?
A little bit nicer, a little bit more user-friendly.
Save it.
Debug compile.
Let's try it now.
Click.
Lock file not found.
Let's open the customer form.
And now, lock file does exist.
So that means I shouldn't be trying to open up the back end and doing stuff and compacting and repairing it and things with molecular structures and moving parts and all that stuff.
Now, that's just an easy way to check and see if the file exists.
Could you open the folder and just look for it?
Yeah, you could.
But now you can programmatically do this too.
If you've got an event, let's say, that runs at midnight or 3 a.m. or whatever, and you know it's going to do stuff and try to work with this file and compact stuff, you could say, "Hey, don't do it because the file exists."
Plus, this is easier than having to go dig for the folder and all that stuff.
But there's other uses for it too.
Now, a few things that I know people are going to mention.
First, this database only has one back-end file.
All of these tables are in here, or links to the same back-end file.
There's just one.
Some of you might have multiple back-end files.
So you could call this function multiple times.
You could call it once, call it twice, call it three times.
I've got databases that have like 20 different back-end files for all different stuff.
Like my email message archiving table, it's all in its own folder because there's lots of them in there.
The stuff that I haven't bothered to move up to SQL Server yet.
Stuff that's not really super important or critical.
So in that case, it would be nice to be able to loop through all of these, wrong window, to loop through all of these tables programmatically.
And instead of me having to give it all 20 back-end names, and you're going to add more yourself in the future, and you're going to then forget to add it to your routine, so it'd be nice if the database could just loop through all the tables in the database and check them automatically.
So that's what we're going to do in the extended cut today for the members.
We're going to upgrade our function so it loops through all of the linked tables in the database and checks each one.
And if there are any duplicates, it'll skip over them.
Silver members and up get access to all of my extended cut videos, not just this one, all of them.
There's hundreds of them.
And Gold members can download these databases.
So click that blue Join button for more information.
Now, before we're done today, what if you need more than just a simple yes-or-no answer?
Sometimes you can inspect the contents of the lock file to find workstation names.
And that can be useful if you know what the computer names are on the network.
For example, this little guy here is a text file.
And as you can see, I've got mine associated with Notepad already.
If I open this up, it just opens up in Notepad.
You can see Picard and Admin.
Admin is the user in the database, which is basically meaningless nowadays.
This used to be for the days way, way back in the day when Access actually had user-level security.
So pretty much everybody's going to show up as Admin.
But Picard is what you're looking for.
That's the name of the computer.
Of course, my computers are named Picard, Spock, Kirk, and all that.
It's me.
But this will give you a list of all the users that are currently attached to this file.
So that'll give you a list of users.
It's not the best foundation for a full user-tracking system, but it's sometimes good enough just to know who's logged in.
Now, I do have another TechHelp video that I did a while back that lets you create a logon-logoff system for your database.
Go watch this video if you want to learn how to do that.
It'll track each user with their computer name and their logged-on name on Windows on that machine, the time they logged on, and the time they logged off.
But essentially, what you'd be looking for then is a logon without a matching logoff.
And, of course, I do have a full security seminar available that shows you not only how to lock down your database, but we also create user and group security inside the database, and you can control who has access to what and see who logged on and when and all that good stuff.
So check this out for more information.
I also want to mention Luke Chung over at FMS.
He's got a product called Total Access Admin.
And if you need real-time administrative monitoring for a larger Access environment, this looks like a really good piece of software.
I'll be honest, I haven't used it myself, but it's Luke Chung, and they put out fantastic stuff.
So check it out.
I'll put a link down below.
To summarize, check to see if you've got a shared back-end file and the LACCDB file is not there, or LDB.
No lock means you're probably clear.
Lock file means you've got to check to make sure that no one's in the database.
No one might be in the database, but you never know.
At least if there's no lock file there, you're probably safe.
You could probably go do what you want to do.
And if you're in it and someone else goes to access it and you're doing design work, they're going to get the warning message that says they can't do stuff because they're going to get that the file is open exclusively by whomever, and that whomever is you, and that's okay.
I've also, and this is just a tangent, built databases before where I used a little text file of my own.
When the database starts up, I can click a little button in my admin database that says, "Turn on admin mode."
And so their database, the very first thing it does when it loads, before it even touches any back-end tables, it checks to see if that admin-mode text file exists in the shared folder.
If it does, it immediately just pops up a warning. It says, "The database is offline. Come back later," and shuts Access down.
That way they don't get in when I'm in the database doing maintenance or whatever.
So if you guys want to see a video on how to do that, let me know.
Post a comment down below.
Maybe we'll make that next week's video.
So there you go.
Now you know how to tell if anyone's using your database.
Are you going to use this?
Post a comment down below.
Let me know.
Let me know how you like today's video.
But that's going to be your TechHelp video for today.
I hope you learned something.
Live long and prosper, my friends.
I'll see you next time.
And members, I'll see you in the extended cut.Quiz Q1. What is the primary purpose of an Access lock file in a shared database environment?
A. It helps Access coordinate multiple users and record locking
B. It encrypts the back-end database
C. It stores backup copies of edited records
D. It prevents all users from opening the front end
Q2. What lock-file extension is normally associated with an ACCDB or ACCDE database?
A. .ldb
B. .laccdb
C. .lock
D. .accdl
Q3. What lock-file extension is normally associated with an older MDB database?
A. .laccdb
B. .mdbx
C. .ldb
D. .lock
Q4. If the shared back-end lock file is not present, what is the most reasonable conclusion?
A. The back end is corrupted
B. No one is probably actively using that back-end file
C. The front end cannot open linked tables
D. All users are definitely logged out of Windows
Q5. If an LACCDB lock file exists, what should you assume before doing maintenance?
A. It is safe to compact the back end immediately
B. The back end must be permanently damaged
C. Someone may still be using the back end
D. The database has been converted to read-only mode
Q6. What is a stale lock file?
A. A lock file left behind after a crash, power loss, or network disconnect
B. A lock file that is more than one year old
C. A lock file created by a front-end database
D. A lock file that contains too many user names
Q7. Why should you be careful before deleting a lock file?
A. Deleting it always repairs the database
B. You could accidentally delete the actual database file instead
C. It automatically deletes all linked tables
D. It changes every user's Windows password
Q8. In a split database, which file should be checked when determining whether shared data may be in use?
A. Each user's local front-end lock file
B. The lock file associated with the shared back end
C. The Windows temporary file folder
D. The Access application executable file
Q9. Why might a user have the front end open without a back-end lock file appearing yet?
A. The front end may be showing an unbound startup form that has not accessed linked tables
B. Access never creates lock files for split databases
C. The user has disabled all VBA code
D. The user has opened the database exclusively
Q10. What VBA function can be used to check whether a particular file exists?
A. FileExists
B. OpenFile
C. Dir
D. LockCheck
Q11. What should a VBA function that checks for a back-end lock file generally return?
A. A Boolean value indicating whether the lock file exists
B. A copy of every record in the back end
C. The password of the current user
D. A list of all database objects
Q12. If a database uses several different back-end files, what is a useful improvement to the simple lock-file check?
A. Delete all lock files before checking
B. Loop through linked tables and check the relevant back-end files
C. Store every back end in the same front-end file
D. Rename every back end to the same filename
Q13. What information might be found by opening an Access lock file as text?
A. Workstation names associated with connected users
B. Full copies of all table records
C. All VBA module source code
D. The database encryption key
Q14. Why is the information in a lock file not a complete user-tracking system?
A. It cannot always distinguish active users from stale connections
B. It prevents users from opening forms
C. It only works with local tables
D. It requires an Internet connection
Q15. What is one way an administrator can prevent users from entering an application during planned maintenance?
A. Create a shared admin-mode text file that the front end checks at startup
B. Delete the front-end database from every workstation
C. Rename every table in the back end
D. Turn off record locking permanently
Answers: 1-A; 2-B; 3-C; 4-B; 5-C; 6-A; 7-B; 8-B; 9-A; 10-C; 11-A; 12-B; 13-A; 14-A; 15-A
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 covers a practical way to determine whether a shared Microsoft Access back-end database may currently be in use before performing maintenance such as compacting and repairing, restoring a backup, replacing files, or changing table designs.
Before making changes to a shared back-end file, you should make sure that nobody is actively using it. You do not want to compact a database or modify its design while another user is entering invoices, processing orders, or editing records. That can lead to data problems and an unpleasant troubleshooting session.
The quickest initial check is to look for the Access lock file. When Access opens a database for normal multi-user use, it generally creates a small companion file in the same folder as the database. For an ACCDB or ACCDE database, this file has the .laccdb extension. For older MDB databases, it uses the .ldb extension.
For example, if your shared back end is named CustomerData.accdb, Access will normally create a file named CustomerData.laccdb while the database is in use.
The lock file is a small working file created by the Access database engine. It helps Access coordinate activity among multiple users, including record locking. If one person is editing a customer record, Access needs a way to help prevent other users from conflicting with that edit. The lock file is part of the internal mechanism Access uses to manage those situations.
Normally, when the final user closes the database properly, Access deletes the lock file automatically. You should not rename, edit, move, or otherwise tamper with these files simply because you see them in the folder. They are not clutter. They are part of Access doing its job.
The important file to check is the lock file for the shared back end, not the one for an individual user's front end. In a properly split database, each user should have a local copy of the front-end database on their own computer. The shared back end should be stored in a network folder or server location. A lock file associated with a local front end does not tell you whether the shared data file is in use.
If the database is stored on a network drive, refresh the folder before relying on what you see. Network folders do not always immediately display the most current file list, so refreshing can help ensure you are looking at the actual current state.
If no .laccdb or .ldb file exists for the shared back end, it is very likely that no one is currently connected to that database file. In that case, you can generally proceed with maintenance. I still recommend opening the back end exclusively before performing design work, compacting, restoring, or replacing files. Opening it exclusively helps prevent someone else from connecting while you are working.
If the lock file does exist, then someone may be using the back end. They may be entering an order, editing a record, running a query, or simply leaving Access open while they are away from their desk. In that situation, treat the lock file as a warning and avoid performing maintenance until you confirm that everyone is out of the database.
However, the presence of a lock file is not a 100 percent guarantee that someone is still actively connected. Access can leave behind a stale lock file if it does not close properly. This may happen if Access crashes, Windows crashes, the computer loses power, a network connection is interrupted, Wi-Fi drops, or a user forcibly shuts down their computer while Access is open.
A stale lock file creates a false positive. You see the file and assume someone is still connected, but nobody actually is.
The safest approach is to confirm that everyone has closed the application. In a small office, that may mean checking with each user. Once you are certain nobody is connected, you can delete the stale lock file. Be extremely careful when doing this. Make sure you are deleting the .laccdb file and not the actual .accdb database file. The filenames are very similar, and deleting the database instead of the lock file would be a serious mistake.
There is also an informal troubleshooting technique that can sometimes help when you suspect a stale lock file. If it is late at night and you reasonably believe everyone is out of the database, you can delete the lock file and wait briefly. If the lock file reappears, someone is definitely still connected. If it remains gone for several minutes, it was probably stale. I would not recommend doing this in the middle of a busy workday or in an environment with many active users. This is more appropriate for a quiet maintenance situation when you are trying to verify whether an old lock file has been left behind.
There is one additional point to understand with split databases. A user can have the front end open without immediately creating a lock file for the back end. This can happen when the startup form is unbound. An unbound form does not directly display or use records from a table or query. If the user opens the application to an unbound main menu and has not yet opened a form, report, query, or other object that touches linked back-end tables, Access may not yet need to connect to the back end.
As soon as the user opens a customer form, processes an order, runs a query, or otherwise accesses a linked table, the back-end lock file will normally appear. In practical terms, the lock file tells you whether the back end is being used, which is the important question before performing maintenance.
I also demonstrate how to build a simple VBA function that checks whether the lock file exists. The function accepts the full path and filename of the back-end ACCDB file, removes the .accdb extension, substitutes .laccdb, and then uses the Dir function to determine whether that lock file exists. The function returns a Boolean value indicating whether the lock file was found.
This is useful because you can call the function from a button on an unbound main menu. If the back end has not yet been accessed, clicking the button can tell you whether another user may already be using the shared data file. The result can be displayed with a message box or with a custom status display on your main menu.
A friendly message such as "Lock file not found" indicates that the back end is probably not in use. A message such as "Lock file does exist" tells you that you should investigate further before performing maintenance.
This type of check can also be useful in automated routines. For example, if you have a scheduled maintenance process that runs at midnight or 3:00 AM, the routine can check for the lock file before attempting to compact a database or perform other work. If the lock file exists, the routine can stop rather than trying to run maintenance while someone may still be connected.
Some databases use more than one back-end file. In that situation, checking a single file may not be enough. You could call the lock-file check for each known back-end database. However, if you have many back ends, this becomes inconvenient and easy to forget when new linked tables are added.
Also, in today's Extended Cut, we will improve the process so it can examine all linked tables in the front end, identify the different back-end files they use, avoid checking duplicate files repeatedly, and determine whether any of those back ends have lock files. This is particularly useful for larger applications that link to multiple Access databases.
If you need more information than a simple yes-or-no answer, you can sometimes inspect the contents of the lock file itself. In many cases, the lock file contains workstation names for computers currently connected to the database. The Access user name may also appear, although it is often not helpful because many systems show the default user name, Admin.
The workstation name can be useful if you know the computer names used in your organization. For example, if the lock file identifies a specific workstation, you may be able to determine who is using that computer and ask them to close the database before maintenance begins.
However, the lock file should not be considered a complete user tracking system. If you need reliable tracking of who logged in, which workstation they used, and when they logged off, you should build a logon and logoff system into your Access application. Such a system can record the Windows user name, the computer name, the logon time, and the logoff time. You can then identify users who have a logon record without a matching logoff record.
For environments that require more advanced administrative monitoring, there are third-party Access tools available that can provide real-time information about users, sessions, locks, and connected databases.
Another useful administrative technique is to use your own maintenance flag file. For example, you can place a small text file in the shared network folder when you need to perform maintenance. When each user's front end starts, before it accesses any linked back-end tables, it checks whether that maintenance file exists. If it does, the application displays a message informing the user that the database is temporarily offline and then closes.
This prevents users from entering the application while you are performing maintenance. It is especially helpful when you need to restore a backup, make table changes, replace the shared back-end file, or compact and repair the database without new users connecting.
To summarize, if the shared back-end lock file is absent, the back end is probably not in use and you can generally proceed with maintenance. If the lock file exists, assume that someone may be connected until you verify otherwise. Remember that stale lock files can remain after crashes or network interruptions, so the file is a helpful warning but not absolute proof that a live user is still connected.
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 Access lock files for shared back ends
LACCDB and LDB lock file names
Checking whether a back end is in use
Stale lock files after crashes or disconnects
Safely removing a stale lock file
Lock file behavior with split databases
Unbound forms and delayed back-end connections
VBA function to detect an LACCDB file
Using Dir to check for a lock file
Adding a lock-file check button to a form
Viewing workstation names in a lock fileArticle Before performing maintenance on a shared Microsoft Access back end, it is important to know whether anyone may still be connected to it. Tasks such as compacting and repairing the file, restoring a backup, changing table designs, or replacing the back-end file should not be done while users are entering or editing data.
A practical first check is to look for Access's lock file in the same folder as the shared database.
When Access opens a database for multi-user use, it normally creates a small companion file. If the database file is named CustomerData.accdb, the companion lock file will normally be named CustomerData.laccdb. For older MDB databases, the companion file uses the .ldb extension instead.
This lock file is part of how Access coordinates multiple users. It helps the database engine manage record locking and other shared-use activity. It is not a file you should normally edit, rename, move, or delete. When the last user closes the database properly, Access normally removes the lock file automatically.
The key distinction is that you should check the shared back-end folder, not each user's front-end folder. In a properly split database, each user has a local copy of the front end on their own computer, while everyone shares the back end containing the tables. A lock file beside a local front end is usually irrelevant to your maintenance decision. The lock file beside the shared back end is the one that matters.
If the back-end lock file is not present, that is usually a strong indication that nobody is actively using the back end. At that point, you can generally proceed with maintenance. It is still wise to open the back end exclusively before making changes, since exclusive mode prevents someone from connecting while you are working.
If the lock file does exist, treat that as a warning that someone may be using the back end. They may be entering orders, editing customer data, running reports, or simply have the application open in the background. Do not assume it is safe to compact, restore, or alter the database just because no one immediately admits to using it.
There is one important limitation: a lock file can be left behind after a crash or connection problem. For example, a user's computer may lose power, Access may stop responding, Windows may crash, or a network connection may fail before Access has an opportunity to remove the lock file. This is often called a stale lock file. A stale lock file can make the database appear to be in use even though nobody is actually connected.
When you find a lock file, the safest approach is to confirm that all users are out of the application. Once you are certain that nobody is connected, you can remove a stale lock file if it remains. Be very careful when doing this. Verify the file extension and full filename before deleting anything. Accidentally deleting the actual back-end database instead of the small lock file can be a serious problem.
In a quiet environment, such as after business hours, some administrators use a simple test when they suspect a stale lock file. They remove the lock file and wait briefly. If it reappears, someone is definitely still connected or reconnects to the database. If it does not return after a reasonable amount of time, it was probably stale. This should not be treated as a normal daytime procedure in a busy database environment, since deleting a live lock file while people are working can create confusion and may interfere with normal operation.
Also remember that a user can have the front-end application open without immediately creating a lock file for the back end. This can happen when the startup form is unbound, meaning it does not display data from a table or query. If the user has not opened a form, report, query, or other object that uses linked back-end tables, Access may not have connected to the back end yet. In that situation, the user technically has the application open but is not yet using the shared data file. For maintenance purposes, the important question is whether the back end itself is in use.
You can also automate the check with VBA. The basic idea is straightforward: provide the full path and filename of the back-end ACCDB file, remove its database extension, replace it with .laccdb, and test whether that file exists. The function should return a simple true or false result indicating whether the lock file was found. Your main menu or administrative form can then display a clear message such as "Lock file found: database may be in use" or "No lock file found."
This type of automated check is useful before scheduled maintenance or administrative tasks. For example, if you have a process that runs overnight, it can first check for the lock file and avoid performing maintenance when the back end appears to be active. The result should still be treated as a safety check rather than absolute proof, since stale lock files are possible.
If your application uses more than one back-end database file, the same idea can be applied to every linked back end. Instead of maintaining a manual list of filenames, a more advanced routine can inspect the linked tables in the front end, determine which back-end files they use, eliminate duplicates, and check each corresponding lock file. This is especially useful in larger applications where different groups of tables are stored in separate back-end files.
It is sometimes possible to inspect the contents of a lock file and find the names of workstations connected to the database. This can help identify where active connections may be coming from. However, this should not be considered a complete user-tracking system. The information may not clearly identify the person using the computer, and it may not help when a stale lock file remains after a crash.
For more reliable tracking, an application can record user logons and logoffs in a table. Each time the front end starts, it can record the Windows username, computer name, and login time. When the user exits normally, it can record the logout time. An administrator can then look for logon records without corresponding logoff records. This is more useful for auditing and troubleshooting, although it still cannot perfectly handle crashes unless additional cleanup or timeout logic is included.
Another useful administrative technique is to create a maintenance mode. Before beginning back-end maintenance, the administrator places a small shared marker file in the network folder. When users start the front end, the startup routine checks for that marker file before opening any linked tables. If maintenance mode is active, the application displays a message that the database is temporarily unavailable and closes. This prevents new users from entering the system while you wait for existing users to exit and complete your maintenance work.
The practical rule is simple. No back-end lock file usually means the shared database is clear for maintenance. A lock file means the database may be in use, so confirm the situation before changing anything. Treat lock files as a useful warning system, not as perfect proof, and always use exclusive access and backups before performing significant work on a shared Access back end. |
