CODEWITHSUNDEEP
c#ms-accessoledb
trashrobber
trashrobber

Reputation: 777

Closing connection to Access DB with OleDb

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!

Upvotes: 4

Views: 4260

Answers (3)

Saeid Alipour
Saeid Alipour

Reputation: 31

I had the same issue and this was the solution : 

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

and now it works.

Upvotes: 3

Leyth G
Leyth G

Reputation: 1143

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.

Upvotes: 2

suff trek
suff trek

Reputation: 39767

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.

Upvotes: 5

Related Questions