File Lock Sharing Violation

[BillD]

I am using Access 2000 and Windows 2000 Pro.
I have been trying to debug a MS Access 2000 DB that has the following issue:

When I open the Database and go to the View/Edit Records Form and then just
close the Form and Database, everything works OK. If I open the database and
go to the View/Edit Records Form and make changes to a record then exit the
Form and Database, the MSACCESS.EXE process remains open (shows up in the
task manager processes) and prevents me from re-opening the database. Not
sure why this process remains active when I exited the database, and often
times, I will end up having multiple MSACCESS.EXE processes open. The only
ways I can Open the Database is by opening Access first and selecting the
Database or by unlocking the .ldb file and deleting it and then ending
MSACCESS.EXE process in Task Manager. .

I have tried multiple message boards with no solution.
In summary: When I close the Access Database, the .ldb file locks and the
process MSACCESS.exe keeps running.
If I try to delete the .ldb file without unlocking it first, I get the
message:
Access.LockFile.9 – Error deleting file – There has been a sharing
violation. I have also determined that the file is opened exclusively by User
“Admin†on Machine “Billâ€.

Thanking you in advance for any assistance to fixing this problem.

 

[Clifford Bass]

Hi Bill,

Sounds to me like the database as some point opens up another hidden
copy of Access (via Automation) and then opens the database also in that
hidden copy. Why it would to that is a mystery. Search your code for
something similar to:

Dim appAccess As New Access.Application

With appAccess
.OpenCurrentDatabase CurrentProject.FullName
'.....
.CloseCurrentDatabase
.Quit
End With
Set appAccess = Nothing

If it does not do the CloseCurrentDatabase and/or the Quit and/or Set
appAccess = Nothing, add those in. If may be good to add in error handling
so that you can make sure the closing, quitting and setting to nothing happen
regardless. If you find a part (or multiple parts) that do this, and you
have trouble fixing them, post the code you find.

Hope this helps,

Clifford Bass

 

[BillD]

I took the database to another computer running Windows Media Center and
Access 2002. The Database worked fine on this computer.
I took a new database template I had and imported all the tables, queries,
froms, reports and module from the Database I was having problems with. This
New Database ended up with the same problems.
I took another copy of the same Database Template and gust imported the
records from the corrupt Database. This Database works great so far.
I deleted the corrupt Database, too much time spent in trying to figure it
out. I have used this Template for yesrs and not had a problem. I don't know
what happened to the one that became corrupted. I would like to know in case
it happens again.
Thanks so much for the advice
Bill D

 

[Clifford Bass]

Hi Bill,

Creating a brand new database and importing all of the objects is the
best way to deal with corruption. That doing so did not cure the problem
would indicate to me that either the corruption is in your Access
installation or is caused by the code that is run by the database when it is
opened or when things within it are opened. That the database worked fine on
another computer would make me lean towards your installation. Perhaps it is
also time to upgrade to the newest version of Access? However, that you can
just import the data into a new copy of the database template, and it works
would make me lean towards the database being corrupt in some way that even
importing does not cure. That is, unless there were changes made to the
forms, reports, queries, macros and/or code in the corrupt version that have
not been made to the template. Best advice--since corruption can happen for
a variety of reasons, including things totally unrelated to Access such as
hardware failure or power outages or lightning strikes, make regular backups.

Clifford Bass

 

[BillD]

Clifford:
The corrupted Database was created before the final Template I am
using.There were many changes made to the template. I do backups regularly
by coping the Database to a Backup folder and renaming the Database with a
date.
The problem seems to point to a Table, Form or Module in the Corrupted
Database since importing them into the New Template ended up with same
problem. Is this possible?
Thanks again for your help.
Bill D.

 

[Clifford Bass]

Hi Bill,

You are quite welcome!

The corruption could happen in any of those. Even with everything
supposedly kosher. I once had a form that had lots of controls--apparently
too many for Access to handle, but Access would let me keep adding far more
controls than it should have. The resultant problem was
intermittent--unexplained crashes and error messages that did not indicate
anything about the real reason. In the code, you can get corruption in the
compiled version, which does not go away without an decompile / recompile
(see <http://www.granite.ab.ca/access/decompile.htm>). Or, of course code
that does not properly clean up after itself. This can be a problem if the
code does not use error handling and something crashes the code in
mid-execution. With a table you might get corrupt data or corrupt indexes.
If the integrity of the objects is not checked by the import process (it may
well be, or it may not--I don't know) it could import bad information. I
recently saw a post about a way to export and import some things as text:

Application.SaveAsText
Application.LoadFromText

See <http://www.datastrat.com/Code/DocDatabase.txt>. Oh, just found a
reference to an writeup on corrupt MDBs at
<http://www.granite.ab.ca/access/corruptmdbs.htm>.

Hope some of that is useful to you.

Clifford Bass

 

[BillD]

Clifford:
I should mention that I have over 200 Databases of the same size as the one
that became corrupted on this 30 Gig D:drive. The operating system and
programs are on a 20 Gig C:drive on the same 800mz machine. I have SQL Server
2005 Express installed as well as Access. This may be a problem. I do not
currently use SQL Server. In the 8 years I have had this machine, I have not
lost a Database before this one. I had to replace Windows Millenium with 2000
Pro two years ago, but never lost any data. I have only had one customer lose
Data in an Access Database because of a total Computer Hard Drive Crash.
Knock on wood.
This is why I like Access 2000 and Windows 2000 Pro. I am hesitant to
upgrade. I have another machine with Office 2002 Pro and a laptop with Office
2007 Pro, but rarely use them, mainly because customers machines are older.

Thanks again for the sites. Some of the Access items will be of good use to
me.

Bill D

 

[Clifford Bass]

Hi Bill,

I can understand staying with what works. Again, you are welcome. And
good luck!

Clifford Bass