OleDbConnection does not release Access database

[Jon]

I have seen a few threads mentioning this problem, but none of them
have offered a solution that works for me. I have an application that
creates an OleDbConnection to a Microsoft Access 2003 database. When
the application is done with the connection, I call the Close() method
on the connection to free up the database resource. After reading
other threads, I even added "OLE DB Services=-4;" to my connection
string to prevent connection pooling, although I gather that it should
have no effect on a local MDB data source. In any case, the
application then attempts to move the database file (in preparation
for a JDO.CompactDatabase call, which seems to be necessary for some
reason), and I get an error because the database file is supposedly
still in use. I tried adding calls to OleDbConnection.Dispose() and
GC.Collect() in desperation, but nothing seems to work.

Here's the offending code:

dbConn = new OleDbConnection();
dbConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;OLE DB
Services=-4;Data Source=" + dbPath;
dbConn.Open();

//
// Use the connection here...
//

// Disconnect from the database
dbConn.Close();
Debug.Assert(dbConn.State == ConnectionState.Closed);
dbConn.Dispose();
dbConn = null;
GC.Collect();

// Prepare to repair the database (due to occasional complex join
problems)
File.Move(dbPath, "c:\\temp_location.mdb"); <---- ***** Error occurs
here *****


Oddly enough, the error indicates that the target file for the move
cannot be accessed:

The process cannot access the file "c:\temp_location.mdb" because it
is being used by another process.


I can see that an LDB file is created when I call dbConn.Open, but it
does not disappear until the application is closed, no matter what I
do to that dbConn object. Does anyone have any suggestions?

Thanks,

Jon

 

[Guest]

Web application?

If so, this is a common problem. One method of getting around this is to
move the data access to another process that has a limited lifetime. In web
apps, you often find the files being held longer than they should be.

Understand also that GC.Collect() does not necessarily mean the GC runs at
that particular second, so you may be attempting a file move prior to the GC
actually running, which would muck up your process.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Jon]

Gregory,

Thanks for the response. Unfortunately, this is not a web app. It's
a simple, stand-alone desktop app that just parses some XML data into
a Microsoft Access database. I just can't find any way to make the
@#$*&^ OleDbConnection let go of the .mdb file. According to the
documentation, one would imagine calling Close() would do the trick,
but no dice. I guess I could try spawning another process to handle
the database connection, but that seems a little excessive for what
should be a very simple little tool.

I finally ended up rewriting the application to create (and
subsequently close) a new OleDbConnection in each method that needs to
work with the database. It's horribly inefficient, but it seems to
avoid the problems with the never-ending connection (which isn't an
entirely accurate description, since the connection claims to be
closed - the mdb file is just never released). There has to be a
better way to handle this, but using a class member OleDbConnection
object just doesn't work for me.

Jon

 

[Paul Clement]

On 30 Sep 2004 06:46:40 -0700, (e-mail address removed) (Jon) wrote:

¤ Gregory,
¤
¤ Thanks for the response. Unfortunately, this is not a web app. It's
¤ a simple, stand-alone desktop app that just parses some XML data into
¤ a Microsoft Access database. I just can't find any way to make the
¤ @#$*&^ OleDbConnection let go of the .mdb file. According to the
¤ documentation, one would imagine calling Close() would do the trick,
¤ but no dice. I guess I could try spawning another process to handle
¤ the database connection, but that seems a little excessive for what
¤ should be a very simple little tool.
¤
¤ I finally ended up rewriting the application to create (and
¤ subsequently close) a new OleDbConnection in each method that needs to
¤ work with the database. It's horribly inefficient, but it seems to
¤ avoid the problems with the never-ending connection (which isn't an
¤ entirely accurate description, since the connection claims to be
¤ closed - the mdb file is just never released). There has to be a
¤ better way to handle this, but using a class member OleDbConnection
¤ object just doesn't work for me.
¤
¤ Jon
¤

It may seem inefficient to open and close an Access database every time you need to fetch or update
data, but it's actually a good idea. One of main contributors to Access database corruption is the
maintenance of persistent connections. The performance loss when opening and closing the database
each time should be negligible.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)