Re: Access Lock Remains after Connection Closed - OLE DB / Jet

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.

"Scott M." <s-mar@xxxxxxxxxxxxx> wrote in message
news:ObwCt%23FpGHA.2464@xxxxxxxxxxxxxxxxxxxxxxx
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.

"XidGarcia" <mithridates@xxxxxxxxx> wrote in message
news:1152560033.584067.56510@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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?

Scott M. wrote:
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?


"XidGarcia" <mithridates@xxxxxxxxx> wrote in message
news:1152557455.889155.118100@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Thanks, anyone have a solution for the problem? Should I be pinging
Microsoft for a bug in JET OLEDB?

Scott M. wrote:
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.

"XidGarcia" <mithridates@xxxxxxxxx> wrote in message
news:1152547204.839361.158650@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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()

Scott M. wrote:
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


"Sanjib Biswas" <sanjib.biswas@xxxxxxxx> wrote in message
news:%237LaJcCpGHA.4216@xxxxxxxxxxxxxxxxxxxxxxx
I am sure you are closing the connection in the following manner,
dispose() -> close() -> setting connection = nothing or
connection =
null.

"XidGarcia" <mithridates@xxxxxxxxx> wrote in message
news:1152536951.828217.90460@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.

XidGarcia wrote:
Unfortunately, that's not it. I am almost frustrated enough
to
rewrite
the DB provider in ODBC. Any more ideas?

Scott M. wrote:
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.



<mithridates@xxxxxxxxx> wrote in message
news:1152221445.642341.228880@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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











.

Relevant Pages

  • Re: Servlet and row-level lock
    ... > them) in which users can select orders from a DB2 database, ... > UPDATEs to the db2 order table; the class mantains a Connection to the ... updates are performed and session closed. ... The row lock is lost. ...
    (comp.lang.java.databases)
  • Re: Access Lock Remains after Connection Closed - OLE DB / Jet
    ... You don't dispose of a connection before you close it, ... The database is an empty template database that is copied ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Access Lock Remains after Connection Closed - OLE DB / Jet
    ... runs in another thread in hopes that if a lock was created, ... that is using the connection, ... If I open an Access 2000 version database and read data from it and at ... close and dispose, the lock still remains. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Connection handling
    ... You state that your data is coming through a data adapter. ... does not go back to the database but simply to the dataset. ... I would not dispose the connection each time unless the ... object also has a dispose method. ...
    (microsoft.public.dotnet.framework.windowsforms.databinding)
  • Re: Connection handling
    ... You state that your data is coming through a data adapter. ... does not go back to the database but simply to the dataset. ... I would not dispose the connection each time unless the ... object also has a dispose method. ...
    (microsoft.public.dotnet.framework.adonet)