Closing connection to Access DB with OleDb

Question

I have an Access Database I'm connecting to with OleDb. Everything is working fine with connection and useage, but I need to make a backup of the file.

I am closing the connection:

  public class myDbHandlerClass
  {

    private OleDbConnection myConnection;
    //in reality this string gets built by properties to the class
    //it ends up being this...
    //Yes Jet 4.0 this is an Access 2003 database
    private string myConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myDatabase.mdb";

    public void OpenDatabase()
    {
      //to open the database
      try
      {
        // Does a previous connection exist?
        if ((myConnection != null) && myConnection.State != ConnectionState.Closed) return;

        //No database connection string is specified, can't continue
        if (string.IsNullOrEmpty(myConnectionString)) return;

        myConnection = new OleDbConnection(myConnectionString);
        myConnection.Open();

      }
      catch (Exception ex)
      {
        ExTrace.WriteLineIf(TraceLog.TraceError, ExTrace.ShowException(ex));
      }
    }

    public void CloseDatabase()
    {
      try
      {
        if ((myConnection == null)) return;

        if (myConnection.State != ConnectionState.Closed) myConnection.Dispose();
        myConnection = null;
        GC.Collect();
      }
      catch (Exception ex)
      {
        ExTrace.WriteLineIf(TraceLog.TraceError, ExTrace.ShowException(ex));
      }
    }
  }

No exception is thrown, connection state == closed, myConnection == null, but the .ldb file never goes away. My subsequent code which is supposed to move the "myDatabase.mdb" file to "myDatabase.bak" fails because the file is already in use - by my program.

How can I ensure it is actually closed and not locked.

EDIT: I modified the code with suggestions from comments below and now it is working.

myConnection.Dispose();

And explicitly calling GC.Collect()

is what got it working.

Thanks for the help!

Chances are its closed but not disposed, it simply goes back into the connection pool. — Tim
– Tim, Commented Aug 7, 2013 at 20:42
I've edited the code to include the declaration and opening of the database. myConnection is declared as a private variable in the class. I've made sure that there is only one instance of the class, so there should only ever be one open connection. — trashrobber
– trashrobber, Commented Aug 7, 2013 at 20:51

Comfortably Numb · Accepted Answer · 2013-08-07 20:53:53Z

5

After myConnection.Close(); try calling myConnection.Dispose();

As a matter of fact, I beleive .Dispose() is closing the connection as well, so simple replacing .Close() with .Dispose() should do the trick.

answered Aug 7, 2013 at 20:53

Comfortably Numb

39.9k17 gold badges82 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

trashrobber Over a year ago

Actually, this is the problem. I added GC.Collect() after the dispose and now it works.

logixologist Over a year ago

@trashrobber, you should set this as your answer and then when you can, accept it.

Saeid Alipour · Accepted Answer · 2015-09-01 11:45:02Z

3

I had the same issue and this was the solution :

GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();

and now it works.

answered Sep 1, 2015 at 11:45

Saeid Alipour

311 bronze badge

Comments

Leyth G · Accepted Answer · 2014-03-17 18:11:53Z

2

Actually, this is the problem. I added GC.Collect() after the dispose and now it works. – trashrobber Aug 7 '13 at 21:14

I had the same issue and this was the solution.

answered Mar 17, 2014 at 18:11

Leyth G

1,1532 gold badges16 silver badges40 bronze badges

Collectives™ on Stack Overflow

Closing connection to Access DB with OleDb

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related