Access Lock Remains after Connection Closed - OLE DB / Jet

  • Thread starter Thread starter mithridates
  • Start date Start date
M

mithridates

I am using a non-pooled OLE DB connection to add records to an access
database. The database is an empty template database that is copied to
the running application directory for a set of objects. For each
objects a set of records are saved to the database, the database is
stored elsewhere, and the clean template file is copied over the
database in the application directory.

I have been having random problems with corruption in this database and
have been trying to track down the issue. In all of the databases that
have been corrupt I have found records belonging to the object that was
processed previously. This has led me to believe that the copy
operation may not be completing or something is remaining in memory
that is making access think it still has the records from the file that
was overwritten.

I have double and triple checked that the connection is being closed
and have had other developers confirm that everything seems to be
working as it is supposed to. I am checking that the connection state
is open, closing and then disposing of the connection. However; the
lock file is remaining after this has happened and remains still after
the file is overwritten by the template to create a clean copy for the
next object to be processed.

I have used the LDB Viewer and confirmed that for each object that is
processed, a user is showing up as accessing the database.

I have tried explicitly turning off connection pooling by adding "OLE
DB Services=-4;" to my connection string, but ths did not solve the
problem. I am open to any suggestions. Thank you for any help that
can be offered.

-Mike
 
You haven't made a connection to Access via the Server Explorer of VS.NET
have you? If you have, then VS.NET will maintain a connection to Access
even when no code is executing.
 
Unfortunately, that's not it. I am almost frustrated enough to rewrite
the DB provider in ODBC. Any more ideas?
 
Any other ideas? I have changed the code such that the connection is
opened prior to insertion of a row and closed / disposed of after the
insertion for each record. The lock goes away most of the time now,
but it still remains about 10% of the time.
 
I am sure you are closing the connection in the following manner,
dispose() -> close() -> setting connection = nothing or connection = null.
 
Uh, that makes no sense.

You don't dispose of a connection before you close it, you close it first.
And, there is no need to set object variables to null or nothing in .NET
since that will happen anyway when the variable falls out of scope
(sometimes you may wish to do this to have the variable go away sooner than
the end of the procedure, but doing it is uneccesary most of the time).

-Scott
 
I have implemented an OLE Db Provider that contains a single
connection. I have a connector class that manages this provider. When
I release the provider, the connector checks if the connection is open,
if so it closes it. It then disposes of the connection, and disposes
of the provider. So the code to close the connection basically looks
like
if (connection.state == open)
{ connection.close }
connection.dispose()
 
FYI, in ADO.NET, you don't need to check a connection's state before closing
it, just call the close method. If it is already closed, it will not throw
an exception.

This is true of most any ADO.NET class that exposes a Close() method.
 
Thanks, anyone have a solution for the problem? Should I be pinging
Microsoft for a bug in JET OLEDB?
 
I have to say, I have used this provider for serveral years against Access
DB's and never encountered the problems you have. Are you 100% sure that
there is no one else connecting to the DB besides your application?
 
I am positive nothing else is using it. It is being placed in a
temporary folder to be built and then copied elsewhere. After being
copied, the lock still remains. The connection (there is only one) is
closed before the copy is executed.

Is it possible an AV program could be causing the lock?
 
Just as a test, add this BEFORE you do your copy and AFTER you have closed
and disposed the connection:

gc.collect

Now, this is not something that you should have to do and it can cause other
issues in your application, I only ask you to try it to rule out the
possibility that is is your connection object that is the source of the
problem.

What I'm thinking is that even though you are cleaning up your connection
correctly, the connection object still resides in memory until the garbage
collector removes it from memory. Calling Dispose() on your connections
removes your application's "hold" on the underlying data source, but because
you are copying the database, you may be copying a locked database.

I don't see how an AV program could be the culprit, but it is easy enough to
check by simply turning of the AV while testing.
 
Here's some observation:

1. If I open an Access 2000 version database and read data from it and at
the end close(), dispose(), the lock goes away immediately after the close()
method.
2. With the same code, if I open an Access 95 version database, after the
close and dispose, the lock still remains. But if I call a MsgBox(), the
lock goes away after when the message box is displayed.

Any idea? I am using VS 2005 with OLEDB provider to open the database.
 
I believe thta 95 and 2000 use different versions of JET, probably has
something to do with that.

I rewrote my process so that the portion of the program using Access
runs in another thread in hopes that if a lock was created, I could
kill the thread and get it to release. Even after killing the thread
that is using the connection, the lock remains!

I am stumped.
 
For an Access 95 database, you need to specify JET 3.51 in your connection
string, not JET 4.0.
 
Back
Top