Lock files

[Carol Giannini]

My apologies in advance for the length of this question.

I am having a problem with Access that seems to result from lock files on
our *server*. We've begun experiencing the "unrecognized database format"
error at least once a week. When it happens, the .ldb file is always visible
in the database folder on the network, but once everyone closes the program
(which, by the way, is run via a front end on each computer that accesses a
database on the network), the .ldb file disappears and we can usually run the
repair function successfully.

On several occasions, however, I have had to call IT for help because the
lock file doesn't delete automatically and I can't delete it manually. On
those times, my IT guy has told me that he can see, in the shared files
folder on the server, 70 lock files (the number seems to stay at 70)
generated by one user. I can't see these files, so am not sure what they
are, but he has had to delete these manually and manually close the user out
of the database before we can get back into the database.

I've read most of the threads on this subject, which seem to pinpoint two
main problems: (1) a user with improper read/write access to the data folder;
and (2) a user who improperly exits Access. I have eliminated the first
completely. Although it's possible that Ctl/Alt/Delete is the culprit,
especially since we started encountering this error very frequently after she
started using the database, I hesitate to jump to conclusions if it could be
caused by something else. (The one thing I have not done is determine that
the problem files on the server are created only by this single user. IT is
going to help me monitor the files for a few days to see whether they appear
from other users as well.)

With that in mind, can someone explain to me what the relationship is, if
any, between the .ldb file in our database folder and the lock files showing
up on the network server?

If a user shuts down his/her computer via Start/Shutdown without closing the
database first, could this cause the problem?

Finally, is there *anyplace* else I should start looking to troubleshoot
this problem?

Thanks in advance for your help.

 

[Immanuel Sibero]

Carol,

main problems: (1) a user with improper read/write access to the data folder;
and (2) a user who improperly exits Access. I have eliminated the first
completely. Although it's possible that Ctl/Alt/Delete is the culprit,

1. May I ask what you did specifically to eliminate the first probable
cause? All users should have full access to the folder where the mdb
resides. You can quickly check this by creating, saving, and deleting a Word
document in that folder while logged in as the user.

2. You may have some corruptions in the database.

3. Is your Access application split? With Front End located in each user's
machine and the Back End on the server?

Immanuel Sibero

 

[Carol Giannini]

Thanks, Joseph. More on this problem: First, I've determined that this user
has all necessary permissions to the data folder, including delete. I don't
think security is the cause, either: all permissions to our database are
granted via groups, and no other members of this user's group appear to be
having or creating problems.

More important: IT just checked this user's computer log for indications
that she's "pushing the button" to shut down, and saw nothing unusual, which
indicates that she's shutting down using the proper Start/Shutdown process.

What else could be causing this problem?

 

[Immanuel Sibero]

Carol,

Never mind about point No. 3 (regarding split database). I can see that you
have a split design. I was reading your post too fast.


Immanuel Sibero

 

[Tom Wickerath]

Hi Carol,

We've begun experiencing the "unrecognized database format"
error at least once a week.

This is a sign of corruption.

When it happens, the .ldb file is always visible in the database folder on
the network, but once everyone closes the program (which, by the way,
is run via a front end on each computer that accesses a database on the
network), the .ldb file disappears and we can usually run the repair function
successfully.

It is good that your users each have their own copy of the front-end. The
..ldb file should be automatically deleted when the last person exits the
database. It sounds like you are not expecting this to happen? Please see
the following KB article:

Introduction to .ldb files
http://support.microsoft.com/kb/299373

On several occasions, however, I have had to call IT for help because
the lock file doesn't delete automatically and I can't delete it manually.

Yes, the server can sometimes place a lock on the file. If I recall
correctly, Cheryl Fischer, our angelic MVP, once posted a message on this
topic.

I've read most of the threads on this subject, which seem to pinpoint
two main problems: (1) a user with improper read/write access to the
data folder; and (2) a user who improperly exits Access.

Add to this list: (3) a database that is starting to corrupt. This can
cause the .ldb file to not be deleted, (4) a user who does not have delete
privileges on the folder (as Joseph mentioned), and (5) a user who improperly
exits the database.

It could be that you are getting some corruption introduced by a bad network
interface card (NIC), or some other piece of hardware in the network. You
could have a user who is exiting improperly, but not admitting this to
anyone. I suggest reading the material that Allen Browne and Tony Toews have
posted on their web sites, if you have not done so already:

Preventing Corruption (Allen Browne)
http://allenbrowne.com/ser-25.html

Corrupt Microsoft Access MDBs FAQ
http://www.granite.ab.ca/access/corruptmdbs.htm

You can use the AppUser utility, available at
http://www.mvps.org/access/modules/mdl0055.htm to try to determine which
user's left the database in a suspect state. I would definately verify that
any suspect users have delete privileges on the folder. Don't just take the
network Admins. word--you should actually test it yourself, from each user's
PC when logged in as that user. Can you successfully Create / Edit / Read /
Delete a text file on the shared folder, using Notepad, when you go around to
each user's PC?

Instead of depending on Compact and Repair, I would create a brand new
database and then import all tables from the existing back-end database into
this new database. Then replace the existing copy of the back-end with this
new copy. Of course, everyone will need to be out of the database when you do
this replacement.

With that in mind, can someone explain to me what the relationship is,
if any, between the .ldb file in our database folder and the lock files
showing up on the network server?

Not sure what you are asking....it is normal to see an .ldb file opened on
the local hard drive for the front-end database, and another .ldb file opened
on the server for the shared back-end database.

If a user shuts down his/her computer via Start/Shutdown without
closing the database first, could this cause the problem?

Absolutely. Without a doubt!

Finally, is there *anyplace* else I should start looking to troubleshoot
this problem?

Have you implemented any method to ensure that each user opens and maintains
a single connection to the back-end database? Do you have any code that
automatically kicks inactive users out, after some predetermined amount of
time? Are any of your users attempting to access your database via a
wireless network? Can your network administrators perform any testing to
verify that the network, including NIC cards in each user's PC, is
functioning properly?


Tom
_____________________________________

:

My apologies in advance for the length of this question.

I am having a problem with Access that seems to result from lock files on
our *server*. We've begun experiencing the "unrecognized database format"
error at least once a week. When it happens, the .ldb file is always visible
in the database folder on the network, but once everyone closes the program
(which, by the way, is run via a front end on each computer that accesses a
database on the network), the .ldb file disappears and we can usually run the
repair function successfully.

On several occasions, however, I have had to call IT for help because the
lock file doesn't delete automatically and I can't delete it manually. On
those times, my IT guy has told me that he can see, in the shared files
folder on the server, 70 lock files (the number seems to stay at 70)
generated by one user. I can't see these files, so am not sure what they
are, but he has had to delete these manually and manually close the user out
of the database before we can get back into the database.

I've read most of the threads on this subject, which seem to pinpoint two
main problems: (1) a user with improper read/write access to the data folder;
and (2) a user who improperly exits Access. I have eliminated the first
completely. Although it's possible that Ctl/Alt/Delete is the culprit,
especially since we started encountering this error very frequently after she
started using the database, I hesitate to jump to conclusions if it could be
caused by something else. (The one thing I have not done is determine that
the problem files on the server are created only by this single user. IT is
going to help me monitor the files for a few days to see whether they appear
from other users as well.)

With that in mind, can someone explain to me what the relationship is, if
any, between the .ldb file in our database folder and the lock files showing
up on the network server?

If a user shuts down his/her computer via Start/Shutdown without closing the
database first, could this cause the problem?

Finally, is there *anyplace* else I should start looking to troubleshoot
this problem?

Thanks in advance for your help.

 

[Carol Giannini]

Immanuel: Re this user's access to the data folder: I personally checked the
properties on the folder and can see that she has all permissions possible.
I then checked with IT to confirm that what I was seeing were the actual
settings; he confirmed that she has all possible permissions.

Re a possible corruption of the database: If that were the case, wouldn't
this problem show up with other users and in other ways? And wouldn't the
repair function fix it? We are synchronized with three outlying offices, and
every night I do a compact/repair before doing the synchronization. I've
never run into a problem there.

Thank you - I really want to track this down.

 

[Mark M]

Along with all the excellent advice already given, you might want to
double-check that all users have the latest patches and service packs
installed and all are using the same Jet engine version.

 

[Immanuel Sibero]

Carol,

From the additional info you gave, it appears that you somehow, more or less
have isolated the culprit to be this one particular user?
In that case, it MAY well be something with that user. I would recommend:

- Make sure this user is the same patch level with the rest of the users
(Access and OS)
- This user's machine (hardware) may be the issue (i.e. network connection,
etc). If you can reproduce the error easily, maybe you can try logging the
problematic user using a different machine, see if the error comes up.


Immanuel Sibero

 

[Carol Giannini]

Tom - these are some really good suggestions. I need to get busy, I see.
But one thing I noticed:

You can use the AppUser utility, available at

I got this utility and ran it. It's reporting that other users have left
the database in a suspect state - including me! Is that an indication of
data corruption?

 

[Tom Wickerath]

Hi Carol,

I got this utility and ran it. It's reporting that other users have left
the database in a suspect state - including me! Is that an indication
of data corruption?

I would have to assume so. Your earlier statement, which read:
We've begun experiencing the "unrecognized database format"
error at least once a week

is definately a sign of corruption. The first thing I would do is to create
a new back-end database, and import all tables into it. Doing so will get you
a brand new set of system tables. These are the normally hidden tables whose
names begin with "MSYS". A compact and repair does not get you a new set of
system tables.

Next, I would verify that all users have the latest patches for the
operating system, Office, and the JET database engine. Here is a KB article
that provides useful information for checking these items:

How to keep a Jet 4.0 database in top working condition
http://support.microsoft.com/kb/303528/


Another article that should be helpful is this one:

How to troubleshoot and to repair a damaged Access 2002 or later database
http://support.microsoft.com/kb/283849/


Are any of the users using a wireless network or a WAN (wide area network)?
Here is a good paper to read on the subject:

Using a wan with ms-access? How fast, how far?
http://www.members.shaw.ca/AlbertKallal/Wan/Wans.html

Is there any chance that you are located in an industrial environment, where
there are lots of electrical motors that can cause voltage sags? If so, an
investment in good quality UPS (Uninterruptable Power Supplies) may be the
best money spent. JET can be extremely finicky to power line upsets. I once
heard of an incident where a JET database suffered corruption on a routine
basis at break time. It turned out that a pop machine was causing a power
line disturbance when a person purchased a can of pop, and this was enough to
bring JET to it's knees.

Please do report back on some of the other questions I asked in my first
reply.


Tom
______________________________________

:

Tom - these are some really good suggestions. I need to get busy, I see.
But one thing I noticed:

You can use the AppUser utility, available at

I got this utility and ran it. It's reporting that other users have left
the database in a suspect state - including me! Is that an indication of
data corruption?

 

[Carol Giannini]

Please do report back on some of the other questions I asked in my first
reply.

Tom: I'm continuing to troubleshoot this problem. I can safely answer no
to most of the questions you asked (no equipment, power fluctuations, etc.),
and all users have the most recent patches, etc. A couple of interesting
things have come up, though, as I've paid more attention to this. One, this
database is used in our main office and in three outlying areas, and all are
synchronized every night. We have never had a problem with synchronizing,
and only our main office is experiencing the lock problem. I don't know
whether synchronization includes the system files, but I suspect not, which
could explain that incongruity.

The most important discovery was that our "problem" user apparently has been
leaving her computer on occasionally overnight, including leaving the
database open (IT found a database session that had been open 26 hours). The
network is programmed to automatically turn off computers at 10 every night,
so there have obviously been a number of improper shutdowns while the
database is open, which undoubtedly explains a whole lot of this.

I didn't build this database (I'm quick to avoid blame, aren't I?), but have
permission from the powers above to start writing code that will monitor use
(which will undoubtedly be the subject of my next question to this group).
I'll also be reconstructing the database in the next couple of days, and will
post a reply with answers to all of your questions and more about what I
find. This is a tough problem to troubleshoot and hopefully someone else
will benefit as well.

Thanks *very much* for all your help.

 

[Tom Wickerath]

Hi Carol,

One, this database is used in our main office and in three outlying areas,
and all are synchronized every night.

Replication is a whole 'nuther issue. Corruption doesn’t always occur during
the synchronization. It most often occurs just while using the replicated
database. I don't have a lot that I can offer in this area. I do know that if
it is not set up correctly, you can run into problems. A lot of developers
avoid using replication because it may not be straight-forward on how to set
it up correctly. My suggestion is to read everything that Michael Kaplan has
posted on the subject of replication, which includes articles # 2, 3, 5-14,
16, 21, 27, and 29 at this site:
http://www.trigeminal.com/usenet/usenet.asp?1033

Here is an MSDN article on the subject:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/dbrepjet.asp

Also, study the replication white paper that is available from Microsoft.
Jeff Conrad, aka the Access Junkie, has included lots of links to replication
topics, including the Microsoft White Paper. You can find Jeff's list here:

http://www.ltcomputerdesigns.com/JCReferences.html#Replication

The suspect state that you asked about earlier could mean any of the
following:

1.) At least one transaction wasn’t completed, yet not rolled back, before
the user exited the database; or

2.) At least one of Jet’s architectural components is bad; or

3.) The VBA code is causing the suspect state by doing something that Jet
can’t handle, like trying to save a database object when the user doesn’t
have exclusive rights to the database because other users are also sharing
the database or another database object is open in the same workspace.

The first two mean corruption, and the last one means the VBA code needs to
avoid causing the problem. A compact/repair will usually fix the first two
if the corruption is caught early enough. In the third case, just delete the
*.LDB file and the *.MDB file should be fine – until the VBA code causes the
problem again, which means the VBA logic needs to be changed to avoid causing
the user to leave the database in a suspect state.

The most important discovery was that our "problem" user apparently
has been leaving her computer on occasionally overnight, including leaving
the database open (IT found a database session that had been open 26 hours).
The network is programmed to automatically turn off computers at 10 every
night, so there have obviously been a number of improper shutdowns while
the database is open, which undoubtedly explains a whole lot of this.

BIG PROBLEM !!!!
In one of my earlier replies, I included suggestions that you add code to:
1.) Establish a single connection for each user and
2.) Add code that kicks inactive users out of the database after a
prescribed amount of time (I use 20 minutes of inactivity as the standard for
databases that I develop).

The second suggestion above would have prevented any problems arising from
the 10:00 PM automated shutdowns.

I didn't build this database (I'm quick to avoid blame, aren't I?)...

There was a funny PowerPoint file that circulated around the internet a few

Thanks *very much* for all your help.

You're welcome. Please sign in to Microsoft's Online Community and answer
yes, on any of the replies that have been helpful, to the question that reads
"Did this post answer the question?"


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
_________________________________________

Please do report back on some of the other questions I asked in my first
reply.

Tom: I'm continuing to troubleshoot this problem. I can safely answer no
to most of the questions you asked (no equipment, power fluctuations, etc.),
and all users have the most recent patches, etc. A couple of interesting
things have come up, though, as I've paid more attention to this. One, this
database is used in our main office and in three outlying areas, and all are
synchronized every night. We have never had a problem with synchronizing,
and only our main office is experiencing the lock problem. I don't know
whether synchronization includes the system files, but I suspect not, which
could explain that incongruity.

The most important discovery was that our "problem" user apparently has been
leaving her computer on occasionally overnight, including leaving the
database open (IT found a database session that had been open 26 hours). The
network is programmed to automatically turn off computers at 10 every night,
so there have obviously been a number of improper shutdowns while the
database is open, which undoubtedly explains a whole lot of this.

I didn't build this database (I'm quick to avoid blame, aren't I?), but have
permission from the powers above to start writing code that will monitor use
(which will undoubtedly be the subject of my next question to this group).
I'll also be reconstructing the database in the next couple of days, and will
post a reply with answers to all of your questions and more about what I
find. This is a tough problem to troubleshoot and hopefully someone else
will benefit as well.

Thanks *very much* for all your help.