looking for a way to unlock a back end mdb file

[Paul]

Is there any way to unlock a back end database file?

I understand that one answer is to get all the users that are linked to it
to close down. However, we're tried that and the file will sometimes remain
locked for a few days.

We're using Access 2003 in a multi-user environment, and every night we
import data from other databases, then compact and repair the back end mdb
file. To ensure that all the users close down their front end file at the
end of every day, I have a timer event in a hidden form that checks the name
of a file on the network server every 30 minutes. If the extension of that
file name is "yes", it closes down. If it's "no", it does nothing.

This works great 99% of the time. All the user front ends close down, the
ldb file disappears and the back end file can be compacted and repaired.
However, every couple of months, this technique doesn't succeed, and the
back end remains locked for several days, during which time we can't compact
and repair it, (because it won't run the compact and repair operation while
it's locked). Thus far the locking never lasts for more than a few days,
after which it will mysteriously unlock itself, and we can resume normal
maintenance operations.

My biggest concern is that it may reach the point where it never unlocks,
and just keeps getting larger over time. I've noticed that even while it's
locked I can copy the back end file to another folder (and copy and repair
the new copy), but I can't delete or copy over the locked file. It would
be great if there were some way to force it to unlock, so we could compact
and repair it every night. Alternatively, if there were a way to delete or
copy over the file, then we could replace it with the copy that was
compacted and repaired in another folder.

Is there any way to solve this problem of the back end mdb file that remains
locked?

Thanks in advance,

Paul

 

[Douglas J. Steele]

The simplest way is to reboot the server. That will force the handle on the
database to be released.

 

[Daniel Pineault]

The technique you employ is the proper approach. I use it on several
multi-user database with no issue.

If it is not managing to shut certain users, then you have another
underlying problem that you need to identify and resolve.

I also believe that certain system dialogs can block your shutdown routine.

Personally I use a combination of automated inactive user logoff and
administrative logoff. So I set the db to automatically boot users off that
do no actually work with the db for 60min and also the method you are using.

I suspect your issue may pertain to a specific pc or 2. Have been able to
identify which user(s) are retaining a lock on the mdb? Is it always the
same pc(s)? Are they up-to-date with both windows and office updates?

When you say that the ldb remains, is it actually in use or did was a
connection improperly closed? Can you manually delete the ldb file? Or does
the system stop you from deleting it because there is still and active
connection?
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Daniel Pineault]

Douglas,

What happens if you reboot while there is a live connection? Will this
possibly risk the integrety of the data?
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Paul]

I suspected that might be the case. However, I also suspect that out
network administrators only reboot on weekends, but I will have to check
with them.

Thanks for pointing that out, Doug.

 

[Paul]

I set the db to automatically boot users off that

do no actually work with the db for 60min and also the method you are
using.

I've found several references on the Web for code that does that. Did you
obtain one of those in the public domain, in which case can you recommend
one, or did you write it yourself?

I suspect your issue may pertain to a specific pc or 2. Have been able to
identify which user(s) are retaining a lock on the mdb?
Is it always the
same pc(s)?

No, but that sounds like a good ided for something I should do. One
challenge I see to that is that while the ldb file shows everyone who logs
on during a session, it doesn't seem to remove their entry when they log
off. So I end up with 20-30 users shown in each ldb file. I've been
thinking of adding a table to keep track of who logs on and off and when
they do it, so I'll be able to tell who is still on at any time.

Are they up-to-date with both windows and office updates?

Presumably, but it's hard to tell if there are exceptions.

When you say that the ldb remains, is it actually in use or was a
connection improperly closed?

I don't know how to determine that.

Can you manually delete the ldb file? Or does
the system stop you from deleting it because there is still and active
connection?

It won't let me delete it.

Hope this helps,

It does. You've given me some new avenues to pursue.

Thanks, Daniel.

 

[Douglas J. Steele]

Well, it all depends on whether something's actually being written at the
time of reboot.

From Paul's description, it sounds as though that may not be the case.

However, you're right that I should have advised caution.

 

[David W. Fenton]

I don't know how to determine that.

On the server, in the Control Panel, go to Administrative Tools and
open the Computer Management control panel. Under SHARED FOLDERS
click on OPEN FILES. That will show you what files are open and who
has them open (though if multiple users have the same file open, I'm
not sure if it's listed multiple times or if the last user is the
only one listed as having it open).

I would suggest that you might want to check if Access is closing
down successfully on all PCs. I just picked up a new client who has
one workstation where when you close Access the MSACCESS.EXE
executable continues running and has to be force closed in Task
Manager. If that's the case on any of the PCs in your situation,
that would mean that the lock on the LDB file might not be closed.

 

[David W. Fenton]

The simplest way is to reboot the server. That will force the
handle on the database to be released.

Rebooting a server is not a SIMPLE solution.

The simplest way to release a file handle is to force close it by
going into the computer management control panel and in Shares,
finding the open file handle and forcing it closed.

 

[David W. Fenton]

=?Utf-8?B?RGFuaWVsIFBpbmVhdWx0?=

What happens if you reboot while there is a live connection? Will
this possibly risk the integrety of the data?

Yes, indeed it can. And it's inconvenient.

Before rebooting, force close the open file(s) through Computer
Management, as I've explained in two other posts.

 

[David W. Fenton]

I suspected that might be the case. However, I also suspect that
out network administrators only reboot on weekends, but I will
have to check with them.

You don't need to reboot. An administrator can force close open file
handles via the Computer Management console in Control Panel. I've
explained it in two other posts.

 

[Daniel Pineault]

I too have that exact scenario, where a pc appear to close access, but the
process msaccess.exe remain active.

I have determined, I think, that it is because the pc is out of date and is
missing numerous updates. Out of my hands though. So I created a script
that kills the process using simple vbs. Not the ideal situation, but until
the IT dept. fixes the situation, it will have to do.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Paul]

You're right, Doug. In my situation, nothing is being written because no
one is in the office.

 

[Paul]

Good information, David. I can't wait to try it out when our network admins
are back in harness, because I don't have direct access to the server
console.

Three questions:

On the server, in the Control Panel, go to Administrative Tools and
open the Computer Management control panel. Under SHARED FOLDERS
click on OPEN FILES.

Am I right in thinking that can only be done on the server's monitor, and
not from my workstation which is only connected to the server through the
Windows XP OS?

one workstation where when you close Access the MSACCESS.EXE
executable continues running and has to be force closed in Task
Manager.

Would that be Task Manager in that particular client workstation (C drive)?

And finally, elsewhere you said

An administrator can force close open file
handles via the Computer Management console in Control Panel.

Don't laugh, but what are open file handles? Would that be the front end
mdb file running on the client's computer?

Thanks

Paul

 

[David W. Fenton]

Am I right in thinking that can only be done on the server's
monitor, and not from my workstation which is only connected to
the server through the Windows XP OS?

If the remote registry editing service is on, it can be done from
any machine that has network access to the server. You just open the
Computer Management console and right click on the top-level node of
the tree and choose CONNECT TO ANOTHER COMPUTER. This will give you
the same view as if you were logged onto the server console. It does
require the same level of administrative access.

Would that be Task Manager in that particular client workstation
(C drive)?

Yes. I don't know that it's possible to remotely kill a process at
all (not sure what the parenthetical C drive reference is about,
though).

And finally, elsewhere you said


Don't laugh, but what are open file handles? Would that be the
front end mdb file running on the client's computer?

The open file handles are what is causing the problem. When an
application uses a file it opens it, and it is so marked by the file
system.

Have you ever tried to clean out your TEMP folder and been told that
files were in use? That's because the files are being held open
(either read or write) by some process on your computer. It's really
very similar to record locking in a database.

If you want to see this on your own computer, download and run
Process Explorer, which is Task Manager on steroids. After you've
done that, go to your TEMP folder and find a file that can't be
deleted. Then in Process Explorer, search for the file name in the
FIND menu. Once it's been located in the running processes/resources
tree, you can force close that file handle if you like (though it's
usually not advisable unless you know something has gone wrong).
More properly, once you've identified what process has it open, you
can close the app in question and that will release the lock on the
file.

 

[Paul]

Thanks for the all the information and suggestions, David.

I downloaded Process Explorer and I've been experimenting with closing open
file handles on back up files, and haven't experienced any corruption or
other problems, so it seems to work just fine. I also discovered that I
don't have Admin rights to the network drives, so I'll either have to get
them, or do some detective work on the individual workstations.

Again, thanks for the help. I should be able to track down and fix the
problems with the ideas you've given me.

Paul

 

[David W. Fenton]

I downloaded Process Explorer and I've been experimenting with
closing open file handles on back up files, and haven't
experienced any corruption or other problems, so it seems to work
just fine.

Don't count on that always being the case, though. It's never good
to force close a file handle as you can't tell from outside what
state the file is in.

I also discovered that I
don't have Admin rights to the network drives, so I'll either have
to get them, or do some detective work on the individual
workstations.

That's to be expected -- you wouldn't want it any other way.