Re: OleDbConnection does not release Access database

From: Jon (google.user_at_mail.com)
Date: 09/30/04 

Date: 30 Sep 2004 06:46:40 -0700

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

"Cowboy (Gregory A. Beamer) - MVP" <NoSpamMgbworld@comcast.netNoSpamM> wrote in message news:<5227B1E3-EDDC-47E6-AC3E-3566DD90DCC5@microsoft.com>...
> 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" wrote:
>
> > 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
> >

Relevant Pages

  • Re: Please Urgent - Update Data Source Problem ???
    ... When the app starts, I'd check to see if the table exists. ... That'd probably be a preferable method to creating the database each time ... If you put connection close ... schedule, and they can update the existing schedule, add new schedule and ...
    (microsoft.public.dotnet.framework.compactframework)
  • ASP.NET data access
    ... We have used ADO datasets previously for a windows application that we developed where the entire db was loaded into the dataset at the beginning of the application and the user made changes to the dataset which was then saved back to the db when the app was shut down. ... Which means that when data is required it is returned from the dataset saving a connection, but has used a heap of resources loading from the db in the first place. ... We would in this case have insert methods which would then use inserts, updates and deletes to directly update the database. ... The problem that this technique seems to have is that you are creating a dataset in each method, which is failur repetitive and may only contain a few rows. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Threads
    ... I have an app that connects to a database every 15 seconds to gather data ... has been mostly developed on a broadband connection. ... with threads/processor time. ...
    (alt.comp.lang.borland-delphi)
  • Re: Albert Kallals article WAN w/Access
    ... connection went down, it could introduce funky stuff into your database. ... Tony Toews, Microsoft Access MVP ...
    (microsoft.public.access.setupconfig)
  • Re: How to manually check for a DB connection at the Form Load event?
    ... you will not have any connections available to the database yet. ... Each instance of the app requires its own connection. ... > information that will be queried and written to a Data Grid on the Web ...
    (microsoft.public.dotnet.languages.vb)
Loading